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()
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,537
Messages
5,572,782
Members
412,484
Latest member
deezina07
Top