Apply Macro on Multiple Worksheets in a Workbook
 

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



Norman Jones Wrote:
Hi Agnes,

To demonstrate that my code operates sequentially on each worksheet
run
this minor adaptation:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
MsgBox sh.name
Next sh

End Sub

You may need to adapt your format code to replace activesheet or shee
names
with the variable sh.

If this is a problem, post your code and I will adjust it in thi
respect.
---
Regards,
Norma

--
Agne
-----------------------------------------------------------------------
Agnes's Profile: http://www.excelforum.com/member.php...fo&userid=1463
View this thread: http://www.excelforum.com/showthread.php?threadid=26261

Apply Macro on Multiple Worksheets in a Workbook
 

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

---
Regards,
Norman



"Agnes" wrote in message
...

Hi Norman,

Your demo below certainly proves that your script works. I'm a
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



Norman Jones Wrote:
Hi Agnes,

To demonstrate that my code operates sequentially on each worksheet,
run
this minor adaptation:

Sub Tester()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
MsgBox sh.name
Next sh

End Sub

You may need to adapt your format code to replace activesheet or sheet
names
with the variable sh.

If this is a problem, post your code and I will adjust it in this
respect.
---
Regards,
Norman


--
Agnes
------------------------------------------------------------------------
Agnes's Profile:
http://www.excelforum.com/member.php...o&userid=14630
View this thread: http://www.excelforum.com/showthread...hreadid=262619