|
Same page setup across multiple sheets (Excel 2000)
I've entered the following code with the view to change the page setup
settings across all the selected worksheets. However, I'm only seeing the settings on the first worksheet. Any ideas re if/how I can incorporate page settings across the board without selecting individual sheets? Thanks in advance ==================== Sheets(Array("PC (Chart)-NI-MONTH", "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")).Select Sheets("PC (Chart)-NI-MONTH").Activate With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With ==================== |
Same page setup across multiple sheets (Excel 2000)
Sub test()
sheetnames = Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV Chart)-NI-R12") For Each sht In sheetnames With Sheets(sht).PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Sarah (OGI)" wrote: I've entered the following code with the view to change the page setup settings across all the selected worksheets. However, I'm only seeing the settings on the first worksheet. Any ideas re if/how I can incorporate page settings across the board without selecting individual sheets? Thanks in advance ==================== Sheets(Array("PC (Chart)-NI-MONTH", "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")).Select Sheets("PC (Chart)-NI-MONTH").Activate With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With ==================== |
Same page setup across multiple sheets (Excel 2000)
Thank you so much!! That works great!
"Joel" wrote: Sub test() sheetnames = Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV Chart)-NI-R12") For Each sht In sheetnames With Sheets(sht).PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Sarah (OGI)" wrote: I've entered the following code with the view to change the page setup settings across all the selected worksheets. However, I'm only seeing the settings on the first worksheet. Any ideas re if/how I can incorporate page settings across the board without selecting individual sheets? Thanks in advance ==================== Sheets(Array("PC (Chart)-NI-MONTH", "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")).Select Sheets("PC (Chart)-NI-MONTH").Activate With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With ==================== |
Same page setup across multiple sheets (Excel 2000)
I dropped a left parethis from the last chart name. If you looking at every
Chart sheet that contained the string "(Chart)" you could use the code below instead of typing each Cart Name in an array. Sub test() For Each sht In ThisWorkbook.Sheets If InStr(sht.Name, "(Chart)") 0 Then With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With End If Next sht End Sub "Sarah (OGI)" wrote: Thank you so much!! That works great! "Joel" wrote: Sub test() sheetnames = Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV Chart)-NI-R12") For Each sht In sheetnames With Sheets(sht).PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Sarah (OGI)" wrote: I've entered the following code with the view to change the page setup settings across all the selected worksheets. However, I'm only seeing the settings on the first worksheet. Any ideas re if/how I can incorporate page settings across the board without selecting individual sheets? Thanks in advance ==================== Sheets(Array("PC (Chart)-NI-MONTH", "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")).Select Sheets("PC (Chart)-NI-MONTH").Activate With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With ==================== |
Same page setup across multiple sheets (Excel 2000)
I'm having the same problem as Sara, below. I have written exactly what you
recommended, except I need this macro to run in any workbook with any number of sheets, without the need to input the individual sheets' names. I simply want the macro to 'Select All Sheets' in the active workbook and apply the specificed format to every worksheet! Thanks! Tondos. "Joel" wrote: I dropped a left parethis from the last chart name. If you looking at every Chart sheet that contained the string "(Chart)" you could use the code below instead of typing each Cart Name in an array. Sub test() For Each sht In ThisWorkbook.Sheets If InStr(sht.Name, "(Chart)") 0 Then With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With End If Next sht End Sub "Sarah (OGI)" wrote: Thank you so much!! That works great! "Joel" wrote: Sub test() sheetnames = Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV Chart)-NI-R12") For Each sht In sheetnames With Sheets(sht).PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Sarah (OGI)" wrote: I've entered the following code with the view to change the page setup settings across all the selected worksheets. However, I'm only seeing the settings on the first worksheet. Any ideas re if/how I can incorporate page settings across the board without selecting individual sheets? Thanks in advance ==================== Sheets(Array("PC (Chart)-NI-MONTH", "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")).Select Sheets("PC (Chart)-NI-MONTH").Activate With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With ==================== |
Same page setup across multiple sheets (Excel 2000)
then just eliminate the IF statement
Sub test() For Each sht In ThisWorkbook.Sheets With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Tondos" wrote: I'm having the same problem as Sara, below. I have written exactly what you recommended, except I need this macro to run in any workbook with any number of sheets, without the need to input the individual sheets' names. I simply want the macro to 'Select All Sheets' in the active workbook and apply the specificed format to every worksheet! Thanks! Tondos. "Joel" wrote: I dropped a left parethis from the last chart name. If you looking at every Chart sheet that contained the string "(Chart)" you could use the code below instead of typing each Cart Name in an array. Sub test() For Each sht In ThisWorkbook.Sheets If InStr(sht.Name, "(Chart)") 0 Then With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With End If Next sht End Sub "Sarah (OGI)" wrote: Thank you so much!! That works great! "Joel" wrote: Sub test() sheetnames = Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV Chart)-NI-R12") For Each sht In sheetnames With Sheets(sht).PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Sarah (OGI)" wrote: I've entered the following code with the view to change the page setup settings across all the selected worksheets. However, I'm only seeing the settings on the first worksheet. Any ideas re if/how I can incorporate page settings across the board without selecting individual sheets? Thanks in advance ==================== Sheets(Array("PC (Chart)-NI-MONTH", "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")).Select Sheets("PC (Chart)-NI-MONTH").Activate With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With ==================== |
Same page setup across multiple sheets (Excel 2000)
Had tried that already, with no results. I needed to change ThisWorkbook. to
ActiveWorkbook. since the macro will be saved on various users' 'Personal' macro workbook. I'm still learning the ropes with VBA! Thanks so much, I appreciate it! "Joel" wrote: then just eliminate the IF statement Sub test() For Each sht In ThisWorkbook.Sheets With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Tondos" wrote: I'm having the same problem as Sara, below. I have written exactly what you recommended, except I need this macro to run in any workbook with any number of sheets, without the need to input the individual sheets' names. I simply want the macro to 'Select All Sheets' in the active workbook and apply the specificed format to every worksheet! Thanks! Tondos. "Joel" wrote: I dropped a left parethis from the last chart name. If you looking at every Chart sheet that contained the string "(Chart)" you could use the code below instead of typing each Cart Name in an array. Sub test() For Each sht In ThisWorkbook.Sheets If InStr(sht.Name, "(Chart)") 0 Then With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With End If Next sht End Sub "Sarah (OGI)" wrote: Thank you so much!! That works great! "Joel" wrote: Sub test() sheetnames = Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV Chart)-NI-R12") For Each sht In sheetnames With Sheets(sht).PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Sarah (OGI)" wrote: I've entered the following code with the view to change the page setup settings across all the selected worksheets. However, I'm only seeing the settings on the first worksheet. Any ideas re if/how I can incorporate page settings across the board without selecting individual sheets? Thanks in advance ==================== Sheets(Array("PC (Chart)-NI-MONTH", "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")).Select Sheets("PC (Chart)-NI-MONTH").Activate With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With ==================== |
Same page setup across multiple sheets (Excel 2000)
Did you change ThisWorkbook to Activeworkbook? I did in the code below.
Sub test() For Each sht In ActiveWorkbook.Sheets With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Tondos" wrote: Had tried that already, with no results. I needed to change ThisWorkbook. to ActiveWorkbook. since the macro will be saved on various users' 'Personal' macro workbook. I'm still learning the ropes with VBA! Thanks so much, I appreciate it! "Joel" wrote: then just eliminate the IF statement Sub test() For Each sht In ThisWorkbook.Sheets With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Tondos" wrote: I'm having the same problem as Sara, below. I have written exactly what you recommended, except I need this macro to run in any workbook with any number of sheets, without the need to input the individual sheets' names. I simply want the macro to 'Select All Sheets' in the active workbook and apply the specificed format to every worksheet! Thanks! Tondos. "Joel" wrote: I dropped a left parethis from the last chart name. If you looking at every Chart sheet that contained the string "(Chart)" you could use the code below instead of typing each Cart Name in an array. Sub test() For Each sht In ThisWorkbook.Sheets If InStr(sht.Name, "(Chart)") 0 Then With sht.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With End If Next sht End Sub "Sarah (OGI)" wrote: Thank you so much!! That works great! "Joel" wrote: Sub test() sheetnames = Array( _ "PC (Chart)-NI-MONTH", _ "PC (Chart)-NI-YTD", _ "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", _ "HH (Chart)-NI-YTD", _ "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", _ "CV (Chart)-NI-YTD", _ "CV Chart)-NI-R12") For Each sht In sheetnames With Sheets(sht).PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With Next sht End Sub "Sarah (OGI)" wrote: I've entered the following code with the view to change the page setup settings across all the selected worksheets. However, I'm only seeing the settings on the first worksheet. Any ideas re if/how I can incorporate page settings across the board without selecting individual sheets? Thanks in advance ==================== Sheets(Array("PC (Chart)-NI-MONTH", "PC (Chart)-NI-YTD", "PC (Chart)-NI-R12", _ "HH (Chart)-NI-MONTH", "HH (Chart)-NI-YTD", "HH (Chart)-NI-R12", _ "CV (Chart)-NI-MONTH", "CV (Chart)-NI-YTD", "CV (Chart)-NI-R12")).Select Sheets("PC (Chart)-NI-MONTH").Activate With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.590551181102362) .BottomMargin = Application.InchesToPoints(0.590551181102362) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 73 End With ==================== |
| All times are GMT +1. The time now is 02:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com