Print Area & Page Set Up

[Metallo]

Hi all,

I have a WB containing 10 WSs.
A block of 5 WSs have the same format as well the remaining 5 WSs also have
the same format. So, basically two formats, 5 + 5.
They are all protected with psw.

The problem I have is to create a macro which automatically set the following:

1) Print Area (A1:AD47) for 5 WSs and (A1:AD53) for the other 5 WSs
2) Fit to 1 Page
3) Centre Horizontally and Vertically

Is is possible to create one macro that the user can click before printing
the 10 WSs? I could link it to a button (called for instance, Print Set Up)
and ask the users to cick the button accordingly.

Or maybe is possible to incorporate the VBA code in the "Workbook Open" part?
I have already other codes in the Workbook Open part, I probably could just
add it after the Unprotect code...

Thank you for your help.

Alex

[Leith Ross]

Hello Alex,

The answer to your question is yes.
________________________________________________________________

Dim I As Long
Dim Wks As Worksheet

For I = 1 To 5

Set Wks = Worksheets(I)

With Wks.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.FitToPagesWide = 1
.PrintArea = "A1:AD47"
End With

Next I


For I = 6 To 10

Set Wks = Worksheets(I)

With Wks.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.FitToPagesWide = 1
.PrintArea = "A1:53"

Next I
________________________________________________________________


Add this code to Button on a User Form or to a Workbook Event

Hope this helps,
Leith Ross

[K Dales]

Each worksheet has a PageSetup property that will let you do what you want -
and I agree that if you already have code in the Workbook_Open procedure, it
would fit in there (but if the workbook is saved with the proper setup, it
should automatically reuse those settings whe it is opened, no?).

In any event (no pun intended):
Dim ThisSheet as worksheet

For each ThisSheet in Worksheets
Select Case ThisSheet.Name ' substitute the actual names below:
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"
' set your page options here for the first group of sheets
Case "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10"
' set the page options for this group of sheets
End Select

For the page options:
With ThisSheet.PageSetup
.PrintArea = "A1:AD47" 'or "A1:AD53"
.FitToPagesTall = True
.FitToPagesWide = True
.CenterHorizontally = True
.CenterVertically = True
End With

I think that should do it... HTH

"Metallo" wrote:

> Hi all,
>
> I have a WB containing 10 WSs.
> A block of 5 WSs have the same format as well the remaining 5 WSs also have
> the same format. So, basically two formats, 5 + 5.
> They are all protected with psw.
>
> The problem I have is to create a macro which automatically set the following:
>
> 1) Print Area (A1:AD47) for 5 WSs and (A1:AD53) for the other 5 WSs
> 2) Fit to 1 Page
> 3) Centre Horizontally and Vertically
>
> Is is possible to create one macro that the user can click before printing
> the 10 WSs? I could link it to a button (called for instance, Print Set Up)
> and ask the users to cick the button accordingly.
>
> Or maybe is possible to incorporate the VBA code in the "Workbook Open" part?
> I have already other codes in the Workbook Open part, I probably could just
> add it after the Unprotect code...
>
> Thank you for your help.
>
> Alex
>

[K Dales]

Oops - correction: FitToPagesTall and FitToPagesWide should each be set to
the value 1, not True!

"Metallo" wrote:

> Hi all,
>
> I have a WB containing 10 WSs.
> A block of 5 WSs have the same format as well the remaining 5 WSs also have
> the same format. So, basically two formats, 5 + 5.
> They are all protected with psw.
>
> The problem I have is to create a macro which automatically set the following:
>
> 1) Print Area (A1:AD47) for 5 WSs and (A1:AD53) for the other 5 WSs
> 2) Fit to 1 Page
> 3) Centre Horizontally and Vertically
>
> Is is possible to create one macro that the user can click before printing
> the 10 WSs? I could link it to a button (called for instance, Print Set Up)
> and ask the users to cick the button accordingly.
>
> Or maybe is possible to incorporate the VBA code in the "Workbook Open" part?
> I have already other codes in the Workbook Open part, I probably could just
> add it after the Unprotect code...
>
> Thank you for your help.
>
> Alex
>

[Metallo]

Hi,

I cannot have it to work, I need to enter next after for, but I don't know
where exactly.

In addition, it is true that once I save the properties they should be kept,
but I don't know why, even if I save the file with the proper page set up,
once I re-open it, the set up is gone.

If you tell me how I can check and fix this, then maybe I don't need the code.

Thank you
Alex

"K Dales" wrote:

> Oops - correction: FitToPagesTall and FitToPagesWide should each be set to
> the value 1, not True!
>
> "Metallo" wrote:
>
> > Hi all,
> >
> > I have a WB containing 10 WSs.
> > A block of 5 WSs have the same format as well the remaining 5 WSs also have
> > the same format. So, basically two formats, 5 + 5.
> > They are all protected with psw.
> >
> > The problem I have is to create a macro which automatically set the following:
> >
> > 1) Print Area (A1:AD47) for 5 WSs and (A1:AD53) for the other 5 WSs
> > 2) Fit to 1 Page
> > 3) Centre Horizontally and Vertically
> >
> > Is is possible to create one macro that the user can click before printing
> > the 10 WSs? I could link it to a button (called for instance, Print Set Up)
> > and ask the users to cick the button accordingly.
> >
> > Or maybe is possible to incorporate the VBA code in the "Workbook Open" part?
> > I have already other codes in the Workbook Open part, I probably could just
> > add it after the Unprotect code...
> >
> > Thank you for your help.
> >
> > Alex
> >

[Metallo]

Hi,

Can anybody help, please?

Thanks
Alex

"Metallo" wrote:

> Hi all,
>
> I have a WB containing 10 WSs.
> A block of 5 WSs have the same format as well the remaining 5 WSs also have
> the same format. So, basically two formats, 5 + 5.
> They are all protected with psw.
>
> The problem I have is to create a macro which automatically set the following:
>
> 1) Print Area (A1:AD47) for 5 WSs and (A1:AD53) for the other 5 WSs
> 2) Fit to 1 Page
> 3) Centre Horizontally and Vertically
>
> Is is possible to create one macro that the user can click before printing
> the 10 WSs? I could link it to a button (called for instance, Print Set Up)
> and ask the users to cick the button accordingly.
>
> Or maybe is possible to incorporate the VBA code in the "Workbook Open" part?
> I have already other codes in the Workbook Open part, I probably could just
> add it after the Unprotect code...
>
> Thank you for your help.
>
> Alex
>