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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
Try using something like this:

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

YOUR STUFF HERE

Next sh
 

KW

Board Regular
Joined
Jan 25, 2005
Messages
167
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
 

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
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
 

KW

Board Regular
Joined
Jan 25, 2005
Messages
167
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,808
Messages
5,833,781
Members
430,232
Latest member
Testsubject

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