Selected sheet(s) Page Setup
 

Hi Folks,

I'm looking to set some page stup options for the selected sheet or sheets
via a macro with will run at Before_Print. I'm sure my problem has a simple
solution but I think I've been looking at it for too long to see it. Because
I want to account to a user selection of potentially more than a single sheet
Im' am trying to work with some sort of activesheet parameter. i've tried a
few combinations of For or With statements and just keep getting different
errors.

Dim ws As Worksheet
For Each ws In ThisWorkbook.ActiveSheet
ws.PageSetup.PrintArea = "$A$1:$U$90"
ws.PageSetup.Orientation = xlLandscape
ws.PageSetup.CenterHorizontally = True
ws.PageSetup.CenterVertically = False
ws.PageSetup.HeaderMargin = Application.InchesToPoints(0)
ws.PageSetup.BottomMargin = Application.InchesToPoints(0.75)
ws.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
ws.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
ws.PageSetup.RightMargin = Application.InchesToPoints(0.25)
ws.PageSetup.TopMargin = Application.InchesToPoints(0.25)
ws.PageSetup.FitToPagesTall = 1
ws.PageSetup.FitToPagesWide = 1
Next aws

Selected sheet(s) Page Setup
 

Dim ws As Worksheet

For Each ws In ActiveWindow.SelectedSheets
With ws.PageSetup
.PrintArea = "$A$1:$U$90"
.Orientation = xlLandscape
.CenterHorizontally = True
.CenterVertically = False
.HeaderMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0.75)
.FooterMargin = Application.InchesToPoints(0.5)
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
Next ws

--
HTH...

Jim Thomlinson


"Stephen" wrote:

Hi Folks,

I'm looking to set some page stup options for the selected sheet or sheets
via a macro with will run at Before_Print. I'm sure my problem has a simple
solution but I think I've been looking at it for too long to see it. Because
I want to account to a user selection of potentially more than a single sheet
Im' am trying to work with some sort of activesheet parameter. i've tried a
few combinations of For or With statements and just keep getting different
errors.

Dim ws As Worksheet
For Each ws In ThisWorkbook.ActiveSheet
ws.PageSetup.PrintArea = "$A$1:$U$90"
ws.PageSetup.Orientation = xlLandscape
ws.PageSetup.CenterHorizontally = True
ws.PageSetup.CenterVertically = False
ws.PageSetup.HeaderMargin = Application.InchesToPoints(0)
ws.PageSetup.BottomMargin = Application.InchesToPoints(0.75)
ws.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
ws.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
ws.PageSetup.RightMargin = Application.InchesToPoints(0.25)
ws.PageSetup.TopMargin = Application.InchesToPoints(0.25)
ws.PageSetup.FitToPagesTall = 1
ws.PageSetup.FitToPagesWide = 1
Next aws

Selected sheet(s) Page Setup
 

I see, said the blind man. this works great and I can use it for a few
different reports to accomplish the same thing. Fantastic.

Thank you for the fast and accurate reply!

"Jim Thomlinson" wrote:

Dim ws As Worksheet

For Each ws In ActiveWindow.SelectedSheets
With ws.PageSetup
.PrintArea = "$A$1:$U$90"
.Orientation = xlLandscape
.CenterHorizontally = True
.CenterVertically = False
.HeaderMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0.75)
.FooterMargin = Application.InchesToPoints(0.5)
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
Next ws

--
HTH...

Jim Thomlinson


"Stephen" wrote:

Hi Folks,

I'm looking to set some page stup options for the selected sheet or sheets
via a macro with will run at Before_Print. I'm sure my problem has a simple
solution but I think I've been looking at it for too long to see it. Because
I want to account to a user selection of potentially more than a single sheet
Im' am trying to work with some sort of activesheet parameter. i've tried a
few combinations of For or With statements and just keep getting different
errors.

Dim ws As Worksheet
For Each ws In ThisWorkbook.ActiveSheet
ws.PageSetup.PrintArea = "$A$1:$U$90"
ws.PageSetup.Orientation = xlLandscape
ws.PageSetup.CenterHorizontally = True
ws.PageSetup.CenterVertically = False
ws.PageSetup.HeaderMargin = Application.InchesToPoints(0)
ws.PageSetup.BottomMargin = Application.InchesToPoints(0.75)
ws.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
ws.PageSetup.LeftMargin = Application.InchesToPoints(0.25)
ws.PageSetup.RightMargin = Application.InchesToPoints(0.25)
ws.PageSetup.TopMargin = Application.InchesToPoints(0.25)
ws.PageSetup.FitToPagesTall = 1
ws.PageSetup.FitToPagesWide = 1
Next aws