The code below switches to Outsheet as soon as it hits the PasteSpecial line. I can make it work by adding "Sheets("InSheet").Select" after each PasteSpecial, but is that really necessary? Oddly, if I step through with the debugger, it works fine, but as soon as I hit F5, it switches sheets.
With Sheets("OutSheet")
Rw = 4
EndRw = Range("A" & Rows.Count).End(xlUp).Row
OutRw = .Range("D" & .Rows.Count).End(xlUp).Row + 1
EndCol = Range("A3").End(xlToRight).Column
Do Until Rw > EndRw
If Cells(Rw, 41).Value > "" Then
Range(Cells(Rw, 41), Cells(Rw, 44)).Copy
.Cells(OutRw, 4).PasteSpecial (xlPasteValues)
Range(Cells(Rw, 46), Cells(Rw, EndCol)).Copy
.Cells(OutRw, 8).PasteSpecial (xlPasteValues)
OutRw = OutRw + 1
End If
Rw = Rw + 1
Loop
End With
Bookmarks