Print Area Macro

KW

Board Regular
Joined
Jan 25, 2005
Messages
167
I have a workbook with some 40 sheets. The print area for each sheet can vary, so I have enetered the range into Cell A1 on each sheet (e.g. $E:$U). I would like to create a macro to skip through each sheet and set the print area before printing. Is there a way of doing this in a loop rather than name each individual sheet name like below:

Code:
Sub PrintArea()

Sheets("Breach Analysis").Select
ActiveSheet.PageSetup.PrintArea = Range("A1").Value
    
Sheets("TimelyEx").Select
ActiveSheet.PageSetup.PrintArea = Range("A1").Value
    
Sheets("ClientMoney").Select
ActiveSheet.PageSetup.PrintArea = Range("A1").Value

End Sub()
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try using something like this:

Set wb = ActiveWorkbook
Dim sh As Worksheet
For Each sh In wb.Sheets

YOUR STUFF HERE

Next sh
 
Upvote 0
I have tried the following code but although it correctly prints the Row Headings, it doesn't seem to print the print areas that I have entered onto each worksheet in cell A1

Code:
Sub PrintAll()
Set wb = ActiveWorkbook
Dim sh As Worksheet
For Each sh In wb.Sheets
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
    End With
        ActiveSheet.PageSetup.PrintArea = Range("A1").Value
Next sh
ActiveWorkbook.PrintOut Copies:=1, Collate:=True

End Sub

Am I doing something wrong?

Would appreciate any help on this.

KW
 
Upvote 0
re

Hi KW try the following code, just past it into the worksheet module.
Each time you adjust a sheet just run this code.

Sub workbook_printarea()
Dim sh As Worksheet
Dim LR As Long, LC As String

For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh
On Error Resume Next
LR = .Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row + 1
If LR = 65537 Then LR = 65536
LC = Chr(.Cells.Find("*", [A1], xlFormulas, xlPart, xlByColumns, xlPrevious, False, False).Column + 64)
.PageSetup.PrintArea = "a$1:$" & LC & "$" & LR
End With
Next
Worksheets(1).Select
End Sub


Cheers Odd
 
Upvote 0
Thanks for your reply. I only need the macro to update the Print Area for each worksheet with the value contained in cell A1 on each sheet though.

I like your code for finding a print area from cell A1 to the last used, but I need to print areas that also contain no data, hence I thought it would be easier to enter the range in a cell on each sheet and get the macro to reference that.

I could do with your help again on his point.

Thanks

KW
 
Upvote 0

Forum statistics

Threads
1,217,757
Messages
6,138,429
Members
450,137
Latest member
HANHAN

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
Back
Top