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
Comments
-
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