VBA Printing in ranges in multipul tabs to printer

[rpike1221]

I am new to the world of VBA. Just trying to print selected areas of tabs in a workbook. So far I have the following however I am getting an error at the first line... I am a little lost at where to start.
I am happy for the pages to print one after each other to the printer or collate then print as one document (if possible) Just cant figure out where i am going wrong.

Any help would be much appreciated...



Sub Weekly_Compliance_Print()
'
' Weekly_Compliance_Print Macro
'
'
Sheets("Banks Asset Allocation Summary").Select
Range("B1:N40").Select
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
ActiveWindow.SmallScroll Down:=-21
Range("P1:Y27").Select
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Sheets("Banks Asset Allocation Summary").Select
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Sheets("Manager weights").Select
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Sheets("Manager Weightings").Select
ActiveWindow.SmallScroll Down:=-12
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Sheets("Manager Fees").Select
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Columns("Y:AG").Select
Range("Y3").Activate
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Sheets("Compliance monitor").Select
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
End Sub

 

[Teeroy]

Hi rpike1221,

The macro recorder is a good place to start but can give very inefficient code. See if the following works for you.

Sub test()
With Sheets("Banks Asset Allocation Summary")
    .PageSetup.PrintArea = .Range("B1:N40")
    .PrintOut
    .PageSetup.PrintArea = .Range("P1:Y27")
    .PrintOut
End With
Sheets("Manager weights").PrintOut
Sheets("Manager Weightings").PrintOut
Sheets("Manager Fees").PrintOut
Sheets("Compliance monitor").PrintOut
End Sub

 

[rpike1221]

Thank you for your help with this. I have made a few changes and so far have the below. Obviously I am missing something because it is not running ! Coming up with Run-Time Error 1004 - which i am guessing has something to do with defining the active sheet ??

Please see below !

Sub test()
With Sheets("Bank 1 Asset Allocation Summary")
.PageSetup.PrintArea = .Range("B1:N40")
.PrintOut
.PageSetup.PrintArea = .Range("P1:Y27")
.PrintOut
End With
With Sheets("Bank 2 Asset Allocation Summary")
.PageSetup.PrintArea = .Range("B1:N47")
.PrintOut
End With
With Sheets("Bank 3 Asset Allocation Summary")
.PageSetup.PrintArea = .Range("B1:K47")
End With
Sheets("Manager weights").PrintOut
Sheets("Manager Weightings").PrintOut
With Sheets("Manager Fees").PrintOut
.PageSetup.PrintArea = .Range("N1:Y106")
.PrintOut
.PageSetup.PrintArea = .Range("AA1:AI103")
.PrintOut
.PageSetup.PrintArea = .Range("AK:AS101")
.PrintOut
End With
Sheets("Compliance monitor").PrintOut
End Sub

thank you !!!
RP

 

[Teeroy]

The line below appears to be in error. You shouldn't have .printout on it.

With Sheets("Manager Fees").PrintOut

Please use [ code ] and [ /code ] tags when inserting blocks of code as it makes it easier to follow. See BB Code List - MrExcel Message Board for details.