format all sheets

  • Thread starter Keep It Simple Stupid
  • Start date
K

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!
 
M

Mike H

You need to select each sheet in turn

For Each mysht In ActiveWorkbook.Worksheets
mysht.Select

Mike
 
D

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.
 
K

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!
 
T

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top