Forum Discussion

dchiggins54's avatar
dchiggins54
Copper Contributor
Aug 29, 2019
Solved

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

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!

excel
  • JKPieterse's avatar
    JKPieterse
    Aug 30, 2019

    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

18 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Aug 30, 2019

    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's avatar
      dchiggins54
      Copper Contributor
      Sep 01, 2019

      Hi JKPieterse,

       

      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!

       

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Sep 01, 2019

        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
    • dchiggins54's avatar
      dchiggins54
      Copper Contributor
      Aug 30, 2019

      JKPieterse 

      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?

       

       
      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Aug 30, 2019

        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?

         

         

Resources