format all sheets

[Keep It Simple Stupid]

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) &
"Signature:_"
.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!

 

[Tom Ogilvy]

That should work.

 

[Mike H]

You need to select each sheet in turn

For Each mysht In ActiveWorkbook.Worksheets
mysht.Select

Mike

 

[Mike H]

No you don't it should work as it is

You need to select each sheet in turn

For Each mysht In ActiveWorkbook.Worksheets
mysht.Select

Mike

 

[Dave Peterson]

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]

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(Before:=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!

 

[Tom Ogilvy]

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(Before:=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

 

[Trevor Shuttleworth]

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