Select All Sheets in Workbook
 

Hi everyone

I recorded a macro to "Select All Sheets" in a workbook, then make a few
column formatting changes and then "Ungroup Sheets".
But when run as a Sub Procedure only the ActiveSheet is changed.
It appears that selecting all sheets and making any changes manually will
work but not when run as a sub Procedure.
Is this a default condition of Excel, if so is there a work around?

TIA

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

Select All Sheets in Workbook
 

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.

HTH. Best wishes Harald


"Robert Christie" skrev i melding
...
Hi everyone

I recorded a macro to "Select All Sheets" in a workbook, then make a few
column formatting changes and then "Ungroup Sheets".
But when run as a Sub Procedure only the ActiveSheet is changed.
It appears that selecting all sheets and making any changes manually will
work but not when run as a sub Procedure.
Is this a default condition of Excel, if so is there a work around?

TIA

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

Select All Sheets in Workbook
 

Thank you Harald

For once it wasnt something I had typed incorrectly.

Regards

Bob C.


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

HTH. Best wishes Harald


"Robert Christie" skrev i melding
...
Hi everyone

I recorded a macro to "Select All Sheets" in a workbook, then make a few
column formatting changes and then "Ungroup Sheets".
But when run as a Sub Procedure only the ActiveSheet is changed.
It appears that selecting all sheets and making any changes manually will
work but not when run as a sub Procedure.
Is this a default condition of Excel, if so is there a work around?

TIA

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

Select All Sheets in Workbook
 

Grouping is not really supported in VBA. You can do some things by
selecting a range of cells and using the selection object (setting the
selection.interior.colorindex works for example), but the best is to loop as
Harald has indicated.

--
Regards,
Tom Ogilvy


"Robert Christie" wrote in message
...
Hi everyone

I recorded a macro to "Select All Sheets" in a workbook, then make a few
column formatting changes and then "Ungroup Sheets".
But when run as a Sub Procedure only the ActiveSheet is changed.
It appears that selecting all sheets and making any changes manually will
work but not when run as a sub Procedure.
Is this a default condition of Excel, if so is there a work around?

TIA

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

Select All Sheets in Workbook
 

Applied Harald's suggested loop and it runs very fast on six formatting cell
on 21 sheets.

Regards
Bob C.

"Tom Ogilvy" wrote:

Grouping is not really supported in VBA. You can do some things by
selecting a range of cells and using the selection object (setting the
selection.interior.colorindex works for example), but the best is to loop as
Harald has indicated.

--
Regards,
Tom Ogilvy


"Robert Christie" wrote in message
...
Hi everyone

I recorded a macro to "Select All Sheets" in a workbook, then make a few
column formatting changes and then "Ungroup Sheets".
But when run as a Sub Procedure only the ActiveSheet is changed.
It appears that selecting all sheets and making any changes manually will
work but not when run as a sub Procedure.
Is this a default condition of Excel, if so is there a work around?

TIA

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

Select All Sheets in Workbook
 

Harald

How or can the freeze panes be included in the loop operation?

Ws.Range("A3").Select
Ws.ActiveWindow.FreezePanes = True

The above code and others I tried wouldn't work.
TIA

Regards Bob C.

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

HTH. Best wishes Harald


"Robert Christie" skrev i melding
...
Hi everyone

I recorded a macro to "Select All Sheets" in a workbook, then make a few
column formatting changes and then "Ungroup Sheets".
But when run as a Sub Procedure only the ActiveSheet is changed.
It appears that selecting all sheets and making any changes manually will
work but not when run as a sub Procedure.
Is this a default condition of Excel, if so is there a work around?

TIA

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

Select All Sheets in Workbook
 

Put
Ws.Activate
above
Ws.Range("A3").Select

I'm not too happy with activation/selection in code, but this is a Window
property and I think you may have to.

HTH. Best wishes Harald

"Robert Christie" skrev i melding
...
Harald

How or can the freeze panes be included in the loop operation?

Ws.Range("A3").Select
Ws.ActiveWindow.FreezePanes = True

Select All Sheets in Workbook
 

Thanks Harald

Im a self taught novice at programming, mostly rely on asking and viewing
posts in this newsgroup for ideas, along with a couple of books. Im trying
to modify and upgrade all my code to speed up and fully automate many
processes
I feel along with other users Im trying or misusing Excel as a database
program. Due to this misuse, many of my difficulties are not covered in
Excels built in help.
But Access is one program that really stumps me.

Thanks again Harald

Regards
Bob C.


"Harald Staff" wrote:

Put
Ws.Activate
above
Ws.Range("A3").Select

I'm not too happy with activation/selection in code, but this is a Window
property and I think you may have to.

HTH. Best wishes Harald

"Robert Christie" skrev i melding
...
Harald

How or can the freeze panes be included in the loop operation?

Ws.Range("A3").Select
Ws.ActiveWindow.FreezePanes = True

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.

Select All Sheets in Workbook
 

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

Select All Sheets in Workbook
 

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

Select All Sheets in Workbook
 

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