VBA: Select Sheet for Page Setup

[NikoleJay]

Hi, again!

I'm on the last step of my project, and I'm probably overthinking it at this point. I am trying to set up the print area, page layout, and width to fit all columns on one page. This report is ran weekly, and the amount of data/number of rows will vary. My documents has two sheets. The first has the buttons I am going to assign the Macros to, and the second sheet has the data. I uploaded a picture in case it helps.

I found examples but it will not work. I am getting a "Compile error: Invalid or unqualified reference" while highlighting the "Sub" line.

Here is the code I tried using for the page setup:

Sub DSR_Print()
    Dim lastRow As Long
    lastRow = Range("A:J").SpecialCells(xlCellTypeLastCell).Row
    Sheets("Data").Select
    ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .PrintArea = "$A$:$J$" & lastRow
        .FitToPagesTall = False
        .FitToPagesWide = 1
End Sub

 

[DanteAmor]

Try this

Sub DSR_Print()
  Dim lastRow As Long
  Sheets("Data").Select
  lastRow = Range("A:J").SpecialCells(xlCellTypeLastCell).Row
  With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .PrintArea = "A1:J" & lastRow
    .FitToPagesTall = False
    .FitToPagesWide = 1
  End With
End Sub

 

[NikoleJay]

Thank you! Everything works except the print area. The width is correct, but it doesn't end after the "signature" cell. I might have been trying to use the wrong command for that.

 

[DanteAmor]

In which column is the signature?

 

[NikoleJay]

Column A

 

[DanteAmor]

Try this:

Sub DSR_Print()
  Dim lastRow As Long
  Sheets("Data").Select
  lastRow = Range("A" & Rows.Count).End(3).Row
  With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .PrintArea = "A1:J" & lastRow
    .FitToPagesTall = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
  End With
End Sub

 

[NikoleJay]

It works! Thank you so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.