CODEWITHSUNDEEP
vbaexcel
Rdster
Rdster

Reputation: 1872

Does .PageSetup.PrintArea only work on Activesheet?

I have a loop that goes through all of the sheets in my workbook...and then sets the print area to whatever button was selected. One button sets it to start at Col A, another Col C...etc.

However, I'm getting a run time error 1004 saying that I've typed a formula wrong. Using the immediate window, I can test that my print area IS what I want it to be.

With sheet
  If .Name <> "Index" Then
    Set rEnd = .Columns("A").Find("END", .[A8], xlValues, xlWhole, xlByRows, xlNext)
    If rEnd Is Nothing Then
      'Not Found - leave as is
    Else
      'Found
      .PageSetup.PrintArea = "$" & sCol & "&8:$R$" & rEnd.Row  '<---Fails here
      SetZoom sheet, "Report Print """ & sCol & """ Scale"
    End If
  End If
End With

Every example I've seen online shows it using ActiveSheet. Does .PageSetup.PrintArea require ActiveSheet? I'd rather not have to activate each sheet if I don't have to.

Upvotes: 0

Views: 127

Answers (2)

tlemaster
tlemaster

Reputation: 859

This gives me a valid PrintArea. Added surrounding quotes and removed extra ampersand.

.PageSetup.PrintArea = "" & "$" & sCol & "8:$R$" & rEnd.Row & ""

Upvotes: 1

jkpieterse
jkpieterse

Reputation: 2986

Setting the print area in fact is the same as adding a sheet-level defined name called Print_Area, so you might use this instead of the .PageSetup.Printarea line:

                .Range("$" & sCol & "$8:$R$" & rEnd.Row).Name = "'" & .Name & "'!Print_Area"

Upvotes: 1

Related Questions