Apply Macro on Multiple Worksheets in a Workbook

A

Agnes

Hi Norman,

Your demo below certainly proves that your script works. I'm
beginner in writing codes. Thanks for being patient with me.

Below are my codes. Please take a look. Thanks again.


Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
'Your format code
Columns("A:A").Select
Selection.ColumnWidth = 6
Range("B12").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Next sh

End Sub


Thanks,
Agnes
 
N

Norman Jones

Hi Agnes,

Try:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
With sh
.Columns("A:A").ColumnWidth = 6
With .PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End With
Next sh
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print area - selection 4
Excel Page Setup on selected worksheets 2
page setup preferances 1
Print sub returns 4
PrintQuality setting - is -3 a valid setting? 3
Printing-don't need half this code but which half?:-) 5
Setting Print Range 2
Print Macro runs "V E R Y S L O W" 3

Top