Format Page setup for multiple worksheet in a workbook
I have multiple (over 30) worksheets in a workbook. I need some VBA that
will go and set the following for all worksheets in the workbook: Font = Arial Font Style = Regular Font Size = 10 Left Margin = 1/2" Right Margin = 1/2" Top Margin = 1" Bottom Margin = 1" Footer = Blank Header = Sept Sales Orientation = Landscape Paper Size = Letter Can anyone help me automate this as I will have this issue every mont. It takes a lot of time to format each sheet manually. Thanks in advance for your help. Cheers. |
Format Page setup for multiple worksheet in a workbook
The For Each, and With operations will make this a breeze for you.
Something like this: Dim current_sheet As Variant 'Loop through each sheet in the workbook For Each current In ThisWorkbook.Sheets 'Format font With current.Cells With .Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 End With End With 'Format page setup With current.PageSetup .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .Orientation = xlLandscape .PaperSize = xlPaperLetter .CenterHeader = "Sept Sales" 'This could and should be modified to become 'dynamic .CenterFooter = "" End With Next current_sheet There you go. D.P. "CB" wrote: I have multiple (over 30) worksheets in a workbook. I need some VBA that will go and set the following for all worksheets in the workbook: Font = Arial Font Style = Regular Font Size = 10 Left Margin = 1/2" Right Margin = 1/2" Top Margin = 1" Bottom Margin = 1" Footer = Blank Header = Sept Sales Orientation = Landscape Paper Size = Letter Can anyone help me automate this as I will have this issue every mont. It takes a lot of time to format each sheet manually. Thanks in advance for your help. Cheers. |
Format Page setup for multiple worksheet in a workbook
Wow there is no means to edit posts?
Sorry i changed one variable name in there from current to current sheet, but didn't change all of them before posting so that will need to be updated. Or you can use this version of it instead. Dim current_sheet As Variant 'Loop through each sheet in the workbook For Each current_sheet In ThisWorkbook.Sheets 'Format font With current_sheet.Cells With .Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 End With End With 'Format page setup With current_sheet.PageSetup .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .Orientation = xlLandscape .PaperSize = xlPaperLetter .CenterHeader = "Sept Sales" 'This could and should be modified to become dynamic .CenterFooter = "" End With Next current_sheet "Derek P." wrote: The For Each, and With operations will make this a breeze for you. Something like this: Dim current_sheet As Variant 'Loop through each sheet in the workbook For Each current In ThisWorkbook.Sheets 'Format font With current.Cells With .Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 End With End With 'Format page setup With current.PageSetup .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .Orientation = xlLandscape .PaperSize = xlPaperLetter .CenterHeader = "Sept Sales" 'This could and should be modified to become 'dynamic .CenterFooter = "" End With Next current_sheet There you go. D.P. "CB" wrote: I have multiple (over 30) worksheets in a workbook. I need some VBA that will go and set the following for all worksheets in the workbook: Font = Arial Font Style = Regular Font Size = 10 Left Margin = 1/2" Right Margin = 1/2" Top Margin = 1" Bottom Margin = 1" Footer = Blank Header = Sept Sales Orientation = Landscape Paper Size = Letter Can anyone help me automate this as I will have this issue every mont. It takes a lot of time to format each sheet manually. Thanks in advance for your help. Cheers. |
Format Page setup for multiple worksheet in a workbook
Sub Macro1()
For Each sht In Sheets With sht.Cells.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .Bold = False .Italic = False .Underline = xlUnderlineStyleNone End With With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .PrintArea = "" .PrintTitleRows = "" .PrintTitleColumns = "" End With Next sht End Sub "CB" wrote: I have multiple (over 30) worksheets in a workbook. I need some VBA that will go and set the following for all worksheets in the workbook: Font = Arial Font Style = Regular Font Size = 10 Left Margin = 1/2" Right Margin = 1/2" Top Margin = 1" Bottom Margin = 1" Footer = Blank Header = Sept Sales Orientation = Landscape Paper Size = Letter Can anyone help me automate this as I will have this issue every mont. It takes a lot of time to format each sheet manually. Thanks in advance for your help. Cheers. |
Format Page setup for multiple worksheet in a workbook
Thank you so much. I will give it a try.
Cheers. "Derek P." wrote: The For Each, and With operations will make this a breeze for you. Something like this: Dim current_sheet As Variant 'Loop through each sheet in the workbook For Each current In ThisWorkbook.Sheets 'Format font With current.Cells With .Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 End With End With 'Format page setup With current.PageSetup .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .Orientation = xlLandscape .PaperSize = xlPaperLetter .CenterHeader = "Sept Sales" 'This could and should be modified to become 'dynamic .CenterFooter = "" End With Next current_sheet There you go. D.P. "CB" wrote: I have multiple (over 30) worksheets in a workbook. I need some VBA that will go and set the following for all worksheets in the workbook: Font = Arial Font Style = Regular Font Size = 10 Left Margin = 1/2" Right Margin = 1/2" Top Margin = 1" Bottom Margin = 1" Footer = Blank Header = Sept Sales Orientation = Landscape Paper Size = Letter Can anyone help me automate this as I will have this issue every mont. It takes a lot of time to format each sheet manually. Thanks in advance for your help. Cheers. |
All times are GMT +1. The time now is 07:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com