Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Same page setup across multiple sheets (Excel 2000)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 128
Default 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
====================
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
====================
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 128
Default 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
====================
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
====================
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default 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
====================
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
====================
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default 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
====================
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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
====================
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I setup page and margins for ALL sheets in an EXCEL file AFTERWARDS? Claudia d'Amato Excel Discussion (Misc queries) 1 September 3rd 08 01:44 PM
setting page setup/print area for multiple sheets [email protected] Excel Discussion (Misc queries) 6 October 27th 07 01:19 PM
Adapting Excel 4 Macro Page Setup for multiple pages SkiBumScot Excel Programming 1 January 3rd 07 01:12 PM
excel 2000, how do i setup sub-sheets ME Excel Worksheet Functions 5 January 8th 05 08:28 AM
Page Setup for multiple sheets MDC[_2_] Excel Programming 5 November 25th 03 08:21 PM


All times are GMT +1. The time now is 08:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement