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!

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!

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!

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!

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

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!

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!

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!