Macro for Page Layout

[chadski778]

I would like a macro for 5 separate page layouts for 5 tabs in my workbook. I have tried a record macro but it seems slow and cumbersome.
For each tab i would like the following:
Sheet1 - Print area A1:J50, landscape, adjust to 65%
Sheet2 - Print area A1:Q25, landscape, adjust to 50%
Sheet3 - Print area A1:F35, landscape, adjust to 100%
Sheet4 - Print Area A1:F50, portrait, adjust to 80%
Sheet5 - Print Area A1:F50, portrait, adjust to 100%

For each layout I would like a footer with:
Left side = filename
Right side = "Created" 'date file created'

I've tried inserting the date before as a footer but each time i open the workbook the date changes to the last time i opened the file. I want the date to remain as the date I created the file.

Thanks

 

[lenze]

Untested, but something like this??

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
Select Case ActiveSheet.CodeName
  Case "Sheet1"
     .PrintArea = "$A$1:$F$10"
     .Orientation = xlLandscape
     .Zoom = 90
 
  Case "Sheet2"
     'etc
End Select
End With
End Sub

lenze

 

[chadski778]

Hi I'd like to put the code on the end of another macro but is asking for a missing End Sub??

How would I get the filename bottom left as footer and 'Created (date of file creation)' bottom right as footer for each sheet in workbook?
p.s.The date must not change each time I open the workbook!

 

[lenze]

Maybe

Case "Sheet1"
   .PrintArea = "$A$1:$F$10"
   .Orientation = xlLandscape
   .Zoom = 90
   .LeftFooter = Me.FullName
   .RightFooter = BuiltinDocumentProperties("Creation Date")

lenze

 

[chadski778]

I get the error message 'Invalid use of ME keyword'

Also can I remove the line

'Private Sub Workbook_BeforePrint(Cancel As Boolean)
this is giving me the error 'Expected End Sub' at the end
of the code from my existing macro

 

[lenze]

Perhaps I should explain. This code is based on an Event Procedure (Namely BeforePrint). It goes in the ThisworkBook module!!! It can not be part of another macro as written. It runs whenever you select print. If you need in with another macro, you can try removing that line and change the Me to ThisWorkBook!!!

lenze

 

[chadski778]

For 'BuiltinDocumenttProperties' there is the following compile error 'Sub or Function not defined'

The following is the code i put at the end of my other macro

With ActiveSheet.PageSetup
Select Case ActiveSheet.CodeName
Case "Composition"
.PrintArea = "$A$1:$J$50"
.Orientation = xlLandscape
.Zoom = 90
.LeftFooter = ThisWorkbook.FullName
.RightFooter = BuiltinDocumentProperties("Creation Date")

End Select
End With

End Sub

Have I missed something here?