SOLVED

Is there a way to use a macro to find a print area when it varies with each run?

Copper Contributor

Hi All...

 

I have a situation where I'm trying to set the print area for 23 different worksheets in one file using a macro.  The columns are the same on each worksheet, but number of rows is different in each one and will vary each time the spreadsheet is run.  When I try to use the standard method for creating a macro to set the print area, it always lists the rows and columns.  Is there a way to program it so it looks for the rows on its own each time?  Am I making any sense?  Happy to elaborate if you can tell me what you need.

 

Any help would be greatly appreciated...thanks!

18 Replies
best response confirmed by dchiggins54 (Copper Contributor)
Solution

@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

@Jan Karel Pieterse 

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?

 

 

@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

@dchiggins54 

 

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?

 

 

@Subodh_Tiwari_sktneer 

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

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

@Jan Karel Pieterse 

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!!

@dchiggins54 

 

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 @Jan Karel Pieterse? That should resolve your issue.

Application.DisplayAlerts = False

 

@Subodh_Tiwari_sktneer 

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!  :)

@dchiggins54 

You're welcome! Glad we could help and your issue is resolved now.

Please accept the solution proposed by @Jan Karel Pieterse as an Answer without any second thought. :)

Hi @Jan Karel Pieterse,

 

Now I need something similar to reformat the same column in each worksheet.  Again, the number of rows is different in each worksheet, so I can't just use the same macro I created for one on all others. It involves adding a working column after column J, dividing column J by $1,000,000, copying the results into the column J, and then deleting the working column.  I know you need to create some type of loop to run through each worksheet again, but my knowledge of the language is minimal.

 

Here's what the initial macro looks like:

 

Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("K2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/1000000"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K59")
Range("K2:K59").Select
Selection.Copy
Range("J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.NumberFormat = "$#,##0.0_);($#,##0.0)"

 

I need to replace the part that shows the range and have it search for the range on each worksheet. Does that makes sense?  If not, please let me know what else you need.

 

Thanks so much!

 

@dchiggins54 

 

Hi Debbie,

 

Please try the following code...

 

Sub ReFormatColumns()
Dim sh          As Worksheet
Dim LastRow     As Long

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With

For Each sh In ThisWorkbook.Worksheets
    LastRow = sh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    sh.Columns("K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    With sh.Range("K2:K" & LastRow)
        .FormulaR1C1 = "=RC[-1]/1000000"
        .Copy
    End With
    With sh.Range("J2:J" & LastRow)
        .PasteSpecial xlPasteValues
        .NumberFormat = "$#,##0.0_);($#,##0.0)"
    End With
    sh.Columns("K").Delete
    Application.CutCopyMode = False
Next sh

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

@Subodh_Tiwari_sktneer 

Thank you so much Subodh!  This works on one worksheet.  However, I need it to work through all 23 of the worksheets.  The same column is on all worksheets and must be converted.  

 

Can you add the loop that is required, please?  I so appreciate your help!  My boss is waiting for this worksheet to be completed today, so I'm so happy you are available.

 

Debbie

@dchiggins54 

There is already a For loop looping through all the sheets in the workbook which contains this macro...

For Each sh In ThisWorkbook.Worksheets

 

If you want to run the macro on ActiveWorkbook, just remove ThisWorkbook from the For loop so that it would become For Each sh In Worksheets

 

Though I didn't test the code earlier, I have just tested it after getting your feedback and it worked on all the sheets not on just one.

@Subodh_Tiwari_sktneer 

Now there's a new error.  Pic attached...what did I do wrong?  I'm running it from the first worksheet in the file.

@dchiggins54 

Do you have any blank sheet in the file?

 

Replace the existing For loop with the following one and let me know if that works for you...

 

 

For Each sh In Worksheets
    If sh.UsedRange.Address(0, 0) <> "A1" Then
        LastRow = sh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        sh.Columns("K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        With sh.Range("K2:K" & LastRow)
            .FormulaR1C1 = "=RC[-1]/1000000"
            .Copy
        End With
        With sh.Range("J2:J" & LastRow)
            .PasteSpecial xlPasteValues
            .NumberFormat = "$#,##0.0_);($#,##0.0)"
        End With
        sh.Columns("K").Delete
        Application.CutCopyMode = False
    End If
Next sh

 

@Subodh_Tiwari_sktneer 

There is one blank sheet at the beginning, however, your updated code worked.  All of the worksheets were done.  Please know that I made the second worksheet active before running the code.  Does it loop back to the beginning?  If so, then that's probably what caused the error.  

 

Thank you so very much for all your help!  You're amazing!  Wish I knew the coding like this, but I just don't.  I tend to understand what I see, but I don't know how to code myself.  When I first wrote to you again about adding the loop, I simply missed the For command.  Thought it was missing since it didn't work but for one worksheet.  Pardon my own ignorance.  I so appreciate you!

@dchiggins54 

You're welcome Debbie! Glad I could help to resolve the issue you were having.

Btw Liking the posts which helped is another way to say thanks. :)

1 best response

Accepted Solutions
best response confirmed by dchiggins54 (Copper Contributor)
Solution

@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

View solution in original post