Forum Discussion
Is there a way to use a macro to find a print area when it varies with each run?
dchiggins54 This macro would set the print-area of each sheet for columns A:N for as many rows as there is data in the sheet:
Sub SetPrintAreas() Dim Sh As Worksheet For Each Sh In Worksheets With Sh.PageSetup .PrintArea = Intersect(Sh.UsedRange, Sh.Range("A:N")).Address End With Next End Sub
dchiggins54 This macro would set the print-area of each sheet for columns A:N for as many rows as there is data in the sheet:
Sub SetPrintAreas()
Dim Sh As Worksheet
For Each Sh In Worksheets
With Sh.PageSetup
.PrintArea = Intersect(Sh.UsedRange, Sh.Range("A:N")).Address
End With
Next
End Sub
Thank you so much for your kind response!
I created the macro (copied/pasted) and I get the error stating that only one cell has been selected (see attached file). I was hoping that with a click of a button on a "master" worksheet in the file, the assigned macro would run through all of the 23 other worksheets and set the print area accordingly to each one. This macro did not accomplish that, I'm afraid.
What am I doing wrong?
Add one line to activate the sheet in your code like this...
For Each Sh In Worksheets Sh.Activate With Sh.PageSetup .PrintArea = Intersect(Sh.UsedRange, Sh.Range("A:N")).Address End With NextAnd when the code produces an error, can you look at the ActiveSheet and confirm if that sheet is empty?
What is the layout of your data on each sheet in the workbook? Is the data scattered all over or it starts from row1 and column A with no rows or columns in between the data?
Hi Subodh,
There is always data on each worksheet. They are never null...at least 10 rows of data or more. The data is not scattered. Starts in A1.
Are you saying to use the code you just gave me instead of the previous version? I ask because the line you gave me in the previous post to add to the macro worked, so I want to be clear.
Debbie
I only added the below line in the proposed code in other post...
Sh.Activate
I added the above line in order to help you know that which sheet is causing the error.
Btw did you add the following line suggested by JKPieterse? That should resolve your issue.
Application.DisplayAlerts = False
dchiggins54 I was assuming the worksheets contain sufficient data, hence the intersection with the usedrange to get the correct number of rows. Apparently one or more worksheets contain just one cell with data in the desginated columns. YOu can avoid the message by adding this line of code to the macro, just below the Dim statement:
Application.DisplayAlerts = False
