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

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

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

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

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