Macro To Look through all tabs and Set Print Area

[Johnny Thunder]

Hello All,


I am working with a workbook that has 12 tabs and a main tab that is the "Report" that gets populated into each tab.

I have macros that look at the "Report Tab" and say filter the report and look for the tab name, upon finding the tab name copy the data on the "Main Report Tab" and paste it into the appropriate Tab with a matching name.

I now need a macro that will go into each tab and set the print area from column B1:Q1 and then look under that until it see's the last bit of data and set the print area to that range, any ideas?

 

[VoG]

Try this in the ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, LR As Long
For Each ws In ActiveWindow.SelectedSheets
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    ws.PageSetup.PrintArea = "A1:Q &LR"
Next ws
End Sub

 

[Johnny Thunder]

Vog,


Thank you for the quick reply. So I put this macro into This Workbook Module and it will automatically set the print range right before I select print every time?

If that's the case this macro is awesome! Thanks!

 

[VoG]

Yep, that is what it does. Or should anyways.

 

[Johnny Thunder]

Vog,


would I just change all the "A's" to cell or column reference B cause I need column A to not be on the print area?

 

[Johnny Thunder]

Vog,


I just tried it an I am getting a run-time error '1004' "The text you entered is not a valid reference or defined name?

it is highlighting, "

ws.PageSetup.PrintArea = "A1:Q &LR"
</pre>

 

[VoG]

Oops!

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, LR As Long
For Each ws In ActiveWindow.SelectedSheets
    LR = ws.Range("B" & Rows.Count).End(xlUp).Row
    ws.PageSetup.PrintArea = "B1:Q" & LR
Next ws
End Sub