copy to another sheet and return to original

colinharwood

Active Member
Joined
Jul 27, 2002
Messages
384
Office Version
  1. 2019
Platform
  1. Windows
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
VBA Code:
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,194
Office Version
  1. 365
Platform
  1. Windows
This will copy from the active sheet
VBA Code:
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

Active Member
Joined
Jul 27, 2002
Messages
384
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,194
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,654
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top