Re: Select All Sheets in Workbook

I am trying your loop that you listed in this post so I can format all sheets
to print better. Here is what I am trying to use, but it isn't working for
some reason.

Dim S As Worksheet
For Each S In ActiveWorkbook.Worksheets
'makes it so all pages print nicely
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.PaperSize = xlPaperLetter
.Zoom = 65
End With
Next

Thanks for your help.

Brian

"Harald Staff" wrote:

> Hi Bob
>
> You're right. I never noticed this. Anyway, you'll have better control
> looping the sheets like this:
>
> Sub test()
> Dim S As Worksheet
> For Each S In ActiveWorkbook.Worksheets
> 'sample actions:
> S.Columns(2).ColumnWidth = 12
> S.Range("B2").Value = Time
> Next
> End Sub
>
> In general macros should not select or activate, they run faster without it
> and the user isn't annoyed by the changing sheets or moving cursors.
>

Brian

Amended code that works on all sheets, not just the Active one.

Public Sub gonl()
Dim S As Worksheet
For Each S In ActiveWorkbook.Worksheets
'makes it so all pages print nicely
With S.PageSetup
.PrintTitleRows = "$1:$1"
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.PaperSize = xlPaperLetter
.Zoom = 65
End With
Next
End Sub


Gord Dibben Excel MVP


On Tue, 20 Dec 2005 13:33:02 -0800, "bttreadwell"
<bttreadwell@discussions.microsoft.com> wrote:

>I am trying your loop that you listed in this post so I can format all sheets
>to print better. Here is what I am trying to use, but it isn't working for
>some reason.
>
>Dim S As Worksheet
>For Each S In ActiveWorkbook.Worksheets
> 'makes it so all pages print nicely
> With ActiveSheet.PageSetup
> .PrintTitleRows = "$1:$1"
> .CenterHorizontally = True
> .CenterVertically = False
> .Orientation = xlPortrait
> .PaperSize = xlPaperLetter
> .Zoom = 65
> End With
> Next
>
>Thanks for your help.
>
>Brian
>
>"Harald Staff" wrote:
>
>> Hi Bob
>>
>> You're right. I never noticed this. Anyway, you'll have better control
>> looping the sheets like this:
>>
>> Sub test()
>> Dim S As Worksheet
>> For Each S In ActiveWorkbook.Worksheets
>> 'sample actions:
>> S.Columns(2).ColumnWidth = 12
>> S.Range("B2").Value = Time
>> Next
>> End Sub
>>
>> In general macros should not select or activate, they run faster without it
>> and the user isn't annoyed by the changing sheets or moving cursors.
>>

Great, that makes sense. I will try it tomorrow. Thanks for your help.

"Gord Dibben" wrote:

> Brian
>
> Amended code that works on all sheets, not just the Active one.
>
> Public Sub gonl()
> Dim S As Worksheet
> For Each S In ActiveWorkbook.Worksheets
> 'makes it so all pages print nicely
> With S.PageSetup
> .PrintTitleRows = "$1:$1"
> .CenterHorizontally = True
> .CenterVertically = False
> .Orientation = xlPortrait
> .PaperSize = xlPaperLetter
> .Zoom = 65
> End With
> Next
> End Sub
>
>
> Gord Dibben Excel MVP
>
>
> On Tue, 20 Dec 2005 13:33:02 -0800, "bttreadwell"
> <bttreadwell@discussions.microsoft.com> wrote:
>
> >I am trying your loop that you listed in this post so I can format all sheets
> >to print better. Here is what I am trying to use, but it isn't working for
> >some reason.
> >
> >Dim S As Worksheet
> >For Each S In ActiveWorkbook.Worksheets
> > 'makes it so all pages print nicely
> > With ActiveSheet.PageSetup
> > .PrintTitleRows = "$1:$1"
> > .CenterHorizontally = True
> > .CenterVertically = False
> > .Orientation = xlPortrait
> > .PaperSize = xlPaperLetter
> > .Zoom = 65
> > End With
> > Next
> >
> >Thanks for your help.
> >
> >Brian
> >
> >"Harald Staff" wrote:
> >
> >> Hi Bob
> >>
> >> You're right. I never noticed this. Anyway, you'll have better control
> >> looping the sheets like this:
> >>
> >> Sub test()
> >> Dim S As Worksheet
> >> For Each S In ActiveWorkbook.Worksheets
> >> 'sample actions:
> >> S.Columns(2).ColumnWidth = 12
> >> S.Range("B2").Value = Time
> >> Next
> >> End Sub
> >>
> >> In general macros should not select or activate, they run faster without it
> >> and the user isn't annoyed by the changing sheets or moving cursors.
> >>
>

Worked perfectly, thank you.

"Gord Dibben" wrote:

> Brian
>
> Amended code that works on all sheets, not just the Active one.
>
> Public Sub gonl()
> Dim S As Worksheet
> For Each S In ActiveWorkbook.Worksheets
> 'makes it so all pages print nicely
> With S.PageSetup
> .PrintTitleRows = "$1:$1"
> .CenterHorizontally = True
> .CenterVertically = False
> .Orientation = xlPortrait
> .PaperSize = xlPaperLetter
> .Zoom = 65
> End With
> Next
> End Sub
>
>
> Gord Dibben Excel MVP
>
>
> On Tue, 20 Dec 2005 13:33:02 -0800, "bttreadwell"
> <bttreadwell@discussions.microsoft.com> wrote:
>
> >I am trying your loop that you listed in this post so I can format all sheets
> >to print better. Here is what I am trying to use, but it isn't working for
> >some reason.
> >
> >Dim S As Worksheet
> >For Each S In ActiveWorkbook.Worksheets
> > 'makes it so all pages print nicely
> > With ActiveSheet.PageSetup
> > .PrintTitleRows = "$1:$1"
> > .CenterHorizontally = True
> > .CenterVertically = False
> > .Orientation = xlPortrait
> > .PaperSize = xlPaperLetter
> > .Zoom = 65
> > End With
> > Next
> >
> >Thanks for your help.
> >
> >Brian
> >
> >"Harald Staff" wrote:
> >
> >> Hi Bob
> >>
> >> You're right. I never noticed this. Anyway, you'll have better control
> >> looping the sheets like this:
> >>
> >> Sub test()
> >> Dim S As Worksheet
> >> For Each S In ActiveWorkbook.Worksheets
> >> 'sample actions:
> >> S.Columns(2).ColumnWidth = 12
> >> S.Range("B2").Value = Time
> >> Next
> >> End Sub
> >>
> >> In general macros should not select or activate, they run faster without it
> >> and the user isn't annoyed by the changing sheets or moving cursors.
> >>
>