copy to another sheet and return to original

[colinharwood]

Hi
I am trying to get the following code to loop through 12 sheets. On each sheet it should copy a range and then paste it on the print sales sheet, starting at the first blank row and then return to the original sheet.
I cant work out how to find the last row on the print sale sheet.
Any help much appreciated

Sub PrintSheetSales()

   Dim lastrow

Application.ScreenUpdating = False

    lastrow = Range("A5000").End(xlUp).Offset(1, 0).Row
    
    Range("A8: M8").Select
    

    Selection.AutoFilter
    ActiveSheet.Range("$A$8:$M$180").AutoFilter Field:=3, Criteria1:="0"
    
    Range("A9:M" & lastrow).Select
    
    Selection.Copy
    
    Sheets("Print Sales").Range("A" & lastrow).PasteSpecial Paste:=xlPasteValues
    
   
    Selection.AutoFilter

End Sub

 

[Fluff]

This will copy from the active sheet

Sub colinharwood()
   With ActiveSheet
      .Range("A8:M8").AutoFilter 3, "0"
      .AutoFilter.Range.Offset(1).Copy
      Sheets("Print Sales").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      .AutoFilterMode = False
   End With
End Sub

Do you want to loop through all sheets in the workbook? If not what sheets are you interested in?
Will the data always be in columns A:M with a header in row 8?
Finally will you always filter col C for "0"?

 

[colinharwood]

Flufff
Thanks very much, it was this line that I could not work out

Sheets("Print Sales").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

All now makes sense
Thanks again

 

[Fluff]

You're welcome & thanks for the feedback.