Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > format all sheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 45
Default format all sheets
I have the following...

For Each mySht In ActiveWorkbook.Worksheets

With mySht.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
mySht.PageSetup.PrintArea = ""
With mySht.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Bold""&14centerheader " & Chr(10) & "HEADER."
.RightHeader = "&""Arial,Italic""as of &D, &T"
.LeftFooter = _
"&""Arial,Italic""&12This is my footer"& Chr(10) & "" & Chr(10) &
"Signatu_"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
Next mySht

It is supposed to format all the sheets in the workbook, but it only works
on the first sheet. I am having the same problem with the rest of the
formatting (changing column widths, hiding cells, etc)

Any help is much appreciated.... I feel like I'm so close, yet so far!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default format all sheets
That should work.

--
Regards,
Tom Ogilvy


"Keep It Simple Stupid" wrote:

I have the following...

For Each mySht In ActiveWorkbook.Worksheets

With mySht.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
mySht.PageSetup.PrintArea = ""
With mySht.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Bold""&14centerheader " & Chr(10) & "HEADER."
.RightHeader = "&""Arial,Italic""as of &D, &T"
.LeftFooter = _
"&""Arial,Italic""&12This is my footer"& Chr(10) & "" & Chr(10) &
"Signatu_"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
Next mySht

It is supposed to format all the sheets in the workbook, but it only works
on the first sheet. I am having the same problem with the rest of the
formatting (changing column widths, hiding cells, etc)

Any help is much appreciated.... I feel like I'm so close, yet so far!

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default format all sheets
You need to select each sheet in turn

For Each mysht In ActiveWorkbook.Worksheets
mysht.Select

Mike

"Keep It Simple Stupid" wrote:

I have the following...

For Each mySht In ActiveWorkbook.Worksheets

With mySht.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
mySht.PageSetup.PrintArea = ""
With mySht.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Bold""&14centerheader " & Chr(10) & "HEADER."
.RightHeader = "&""Arial,Italic""as of &D, &T"
.LeftFooter = _
"&""Arial,Italic""&12This is my footer"& Chr(10) & "" & Chr(10) &
"Signatu_"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
Next mySht

It is supposed to format all the sheets in the workbook, but it only works
on the first sheet. I am having the same problem with the rest of the
formatting (changing column widths, hiding cells, etc)

Any help is much appreciated.... I feel like I'm so close, yet so far!

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default format all sheets
No you don't it should work as it is

"Mike H" wrote:

You need to select each sheet in turn

For Each mysht In ActiveWorkbook.Worksheets
mysht.Select

Mike

"Keep It Simple Stupid" wrote:

I have the following...

For Each mySht In ActiveWorkbook.Worksheets

With mySht.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
mySht.PageSetup.PrintArea = ""
With mySht.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Bold""&14centerheader " & Chr(10) & "HEADER."
.RightHeader = "&""Arial,Italic""as of &D, &T"
.LeftFooter = _
"&""Arial,Italic""&12This is my footer"& Chr(10) & "" & Chr(10) &
"Signatu_"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
Next mySht

It is supposed to format all the sheets in the workbook, but it only works
on the first sheet. I am having the same problem with the rest of the
formatting (changing column widths, hiding cells, etc)

Any help is much appreciated.... I feel like I'm so close, yet so far!

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default format all sheets
Any chance that the activeworkbook isn't what you wanted it to be?

Maybe adding:

For Each mySht In ActiveWorkbook.Worksheets
msgbox mysht.range("a1").address(external:=true)
...

Would give you a hint.

Keep It Simple Stupid wrote:

I have the following...

For Each mySht In ActiveWorkbook.Worksheets

With mySht.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
mySht.PageSetup.PrintArea = ""
With mySht.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Bold""&14centerheader " & Chr(10) & "HEADER."
.RightHeader = "&""Arial,Italic""as of &D, &T"
.LeftFooter = _
"&""Arial,Italic""&12This is my footer"& Chr(10) & "" & Chr(10) &
"Signatu_"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
Next mySht

It is supposed to format all the sheets in the workbook, but it only works
on the first sheet. I am having the same problem with the rest of the
formatting (changing column widths, hiding cells, etc)

Any help is much appreciated.... I feel like I'm so close, yet so far!

--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 45
Default format all sheets
You know, when I run it separately, it DOES work!
I guess my problem is something is happening that I get "Run-time
error'91'-Object variable or With block variable not set."
When I click DEBUG, it highlights:

mySht.Name=CStr(myCell.Value) in the following:

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(CStr(myCell.Value)).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = CStr(myCell.Value)
With myCell.CurrentRegion
..AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
..SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
..AutoFilter
End With
Resume
SheetExists:
Next myCell

(I am using this code to try to group data sets and create new sheets). I
don't know what I am doing wrong - I am thinking about starting over!




"Keep It Simple Stupid" wrote:

I have the following...

For Each mySht In ActiveWorkbook.Worksheets

With mySht.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
mySht.PageSetup.PrintArea = ""
With mySht.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Bold""&14centerheader " & Chr(10) & "HEADER."
.RightHeader = "&""Arial,Italic""as of &D, &T"
.LeftFooter = _
"&""Arial,Italic""&12This is my footer"& Chr(10) & "" & Chr(10) &
"Signatu_"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
Next mySht

It is supposed to format all the sheets in the workbook, but it only works
on the first sheet. I am having the same problem with the rest of the
formatting (changing column widths, hiding cells, etc)

Any help is much appreciated.... I feel like I'm so close, yet so far!

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default format all sheets
Dim sh as Worksheet
For Each myCell In myArea
set sh = Nothing
On Error Resume Next
set sh = Worksheets(CStr(myCell.Value))
On Error goto 0
if sh is nothing then

Set sh = Worksheets.Add(Befo=Worksheets(1))
sh.Name = CStr(myCell.Value)
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
sh.Range("A1")
sh.Cells.EntireColumn.AutoFit
.AutoFilter
End With
End if
Next myCell

--
Regards,
Tom Ogilvy

"Keep It Simple Stupid" wrote:

You know, when I run it separately, it DOES work!
I guess my problem is something is happening that I get "Run-time
error'91'-Object variable or With block variable not set."
When I click DEBUG, it highlights:

mySht.Name=CStr(myCell.Value) in the following:

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(CStr(myCell.Value)).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = CStr(myCell.Value)
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

(I am using this code to try to group data sets and create new sheets). I
don't know what I am doing wrong - I am thinking about starting over!




"Keep It Simple Stupid" wrote:

I have the following...

For Each mySht In ActiveWorkbook.Worksheets

With mySht.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
mySht.PageSetup.PrintArea = ""
With mySht.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Bold""&14centerheader " & Chr(10) & "HEADER."
.RightHeader = "&""Arial,Italic""as of &D, &T"
.LeftFooter = _
"&""Arial,Italic""&12This is my footer"& Chr(10) & "" & Chr(10) &
"Signatu_"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
Next mySht

It is supposed to format all the sheets in the workbook, but it only works
on the first sheet. I am having the same problem with the rest of the
formatting (changing column widths, hiding cells, etc)

Any help is much appreciated.... I feel like I'm so close, yet so far!

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,089
Default format all sheets
You're missing an "End With" before the "Next mySht"

However, I wouldn't expect it to compile so I'm not sure why it's only doing
one sheet.

With the missing End With in place it worked fine for me ... though very
slow. I'd be inclined to remove any statements that are simply the default
values.

Regards

Trevor


"Keep It Simple Stupid" wrote
in message ...
I have the following...

For Each mySht In ActiveWorkbook.Worksheets

With mySht.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
mySht.PageSetup.PrintArea = ""
With mySht.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Bold""&14centerheader " & Chr(10) & "HEADER."
.RightHeader = "&""Arial,Italic""as of &D, &T"
.LeftFooter = _
"&""Arial,Italic""&12This is my footer"& Chr(10) & "" & Chr(10) &
"Signatu_"
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
Next mySht

It is supposed to format all the sheets in the workbook, but it only works
on the first sheet. I am having the same problem with the rest of the
formatting (changing column widths, hiding cells, etc)

Any help is much appreciated.... I feel like I'm so close, yet so far!



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
format cells in 30 different sheets Pat Excel Discussion (Misc queries) 3 March 15th 07 05:38 PM
Format cells the same in two sheets [email protected] Excel Programming 2 September 28th 06 06:44 PM
format 10 sheets on same file to look like first one Michael Abraham New Users to Excel 1 May 20th 06 10:38 AM
Format all sheets in one Workbook Joe_bruin Excel Discussion (Misc queries) 5 December 6th 05 05:54 PM
Format multiple sheets in VBA DrDave1958 Excel Programming 2 October 22nd 03 01:35 AM


All times are GMT +1. The time now is 10:09 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement