Print Area Macro

[KW]

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:

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

 

[colbymack]

Try using something like this:

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

YOUR STUFF HERE

Next sh

 

[KW]

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

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]

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]

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