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
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
Next
And 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
I'm sorry...I was confused. I only just realized that two people were helping me here. Subodh, I added your line of code and to Jan's macro and everything works great now. I apologize for the confusion.
Bottom line, I have a macro that does exactly what I need it to do. There are no longer any errors. My guess is, though, that perhaps I should add that code that detects a null and produces an error...just in case. Perhaps I will should the time come. I will certainly save this, because you have both been so very helpful.
I just don't know how to mark my post as answered. It took more than one response.
Thanks again! 🙂
- The message you got suggests otherwise, check all worksheets, there should be 1 or more which have just a small number of cells filled within columns A:N
Ok, there's some confusion. I'm not getting the error message anymore. The code I was provided is working perfectly now.
Thank you so much for your kind assistance! You have been very helpful! Thank you!!