VBA dynamically set print area for all worksheets

sagarshah

New Member
Joined
Jan 25, 2017
Messages
10
Hello,
I have 30 worksheets.
I want to create a dynamic print range across multiple worksheet within one workbook.
The following code works however I need to run this macro once at a time for all sheets.

1. Is there a way to automatically run this macro across all worksheets?
2. Is there a way to automatically run this macro for only a number of worksheets (or not run for some worksheets)

Sub PrintArea()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
While Len(Cells(LastRow, 1).Value) = 0
LastRow = LastRow - 1
Wend
Range("A1:I" & LastRow).Name = "Print_Area"
'ActiveSheet.PrintOut
End Sub

Thanks.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
The (Untested) code below should apply the code that you posted to all sheets with excepted sheets. See the comments for all sheets.

VBA Code:
Sub PrintArea()
    Dim LastRow As Long, ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If ws.Name <> "Sheet1" Or ws.Name <> "Master" Then
                ' Change sheet names in the above line for sheets that you don't want included.
                ' Remove the line above if you want all sheets
                LastRow = .Range("A" & Rows.Count).End(xlUp).Row
                While Len(.Cells(LastRow, 1).Value) = 0
                    LastRow = LastRow - 1
                Wend
                .Range("A1:I" & LastRow).Name = "Print_Area"
                'ws.PrintOut
            End If  'Remove this line if you want all sheets
        End With
    Next
End Sub
 
Upvote 0
Hi there. This code will do your macro (slightly modified) for every sheet except those listed in the Excludes array.
VBA Code:
Sub PrintArea()
    Dim LastRow As Long
    Dim Sht As Worksheet
    Dim Excludes As Variant
    Excludes = Array("Sheet3", "Sheet1")
    For Each Sht In ActiveWorkbook.Worksheets
        With Sht
            If IsError(Application.Match(Sht.Name, Excludes, 0)) Then
                LastRow = .Range("A" & Rows.Count).End(xlUp).Row
                While Len(.Cells(LastRow, 1).Value) = 0
                    LastRow = LastRow - 1
                Wend
                .Names.Add Name:="Print_Area", RefersTo:=.Range("A1:I" & LastRow)
            End If
        End With
    Next Sht
    'ActiveSheet.PrintOut
End Sub
 
Upvote 0
You're welcome. Out of interest, which solution did you end up with?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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