 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Same 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 ==================== 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 > 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 [quoted text clipped - 38 lines] > End With > ==================== Thank you so much!! That works great! > Sub test() > [quoted text clipped - 83 lines] > > End With > > ==================== 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 > Thank you so much!! That works great! > [quoted text clipped - 85 lines] > > > End With > > > ==================== 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. > 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 [quoted text clipped - 125 lines] > > > > End With > > > > ==================== 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 > 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 [quoted text clipped - 132 lines] > > > > > End With > > > > > ==================== 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! > then just eliminate the IF statement > [quoted text clipped - 168 lines] > > > > > > End With > > > > > > ==================== 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 > Had tried that already, with no results. I needed to change ThisWorkbook. to > ActiveWorkbook. since the macro will be saved on various users' 'Personal' [quoted text clipped - 173 lines] > > > > > > > End With > > > > > > > ==================== |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.