Need to add paste special condition a copy.destination for loop

13,298
       I need to use a paste special:
       WS.Range("A42", "I42").Rows.Copy _
       Destination:=Rept.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

In such a case you do not use the above method. You use this

WS.Range("A42", "I42").Rows.Copy

Rept.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Share:
13,298
Alistair Weir
Author by

Alistair Weir

Data analyst

Updated on June 04, 2022

Comments

  • Alistair Weir
    Alistair Weir almost 2 years

    I have a range of data that is in the same position in every worksheet in a book and will always be in that position. When the macro is run the data should be copied and added to a report sheet. I have that part working but I need to use a paste special:

    .PasteSpecial xlPasteValues
    

    as there are formulas in the range. I am unsure where to add the paste special condition in this code, since I'm using .Copy, Destination.

    Option Explicit
    Sub CreateTempPSDReport()
    
        Dim WS As Worksheet, Rept As Worksheet
    
        Set Rept = Sheets("Temporary PSD Report")
    
        Application.ScreenUpdating = False
    
        '--> Loop through each worksheet except the report and
        '--> Copy the set range to the report
        For Each WS In ThisWorkbook.Worksheets
            If Not WS.Name = "Temporary PSD Report" Then
                WS.Range("A42", "I42").Rows.Copy _
                Destination:=Rept.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next
    
        Application.ScreenUpdating = True
    
    End Sub