Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Select - is

[stevebausch]

Found it in a recorded macro; hold down Control, select the sheets.
From that point, what ever you enter in a cell is echoed to that cell location on the other sheets.

I find it quite clever, but I hate to use anything without understanding it.

Is it safe to say that the Wizards understand this line of code forwards and backwards?

Could you help us ordinary folk?

What is going on in this line of code? What's the easy way of referring to this technique?
Are there any possible problems with it?

Thanks,
Steve

 

[RichardS]

Steve,

I recorded one on a large file, and it names each sheet by it's tab name. Would take a bit of writing, recording probably the only safe way to go.

I believe it is called grouping sheets.

Are there any problems? I have used the technique a number of times, but not within a macro, just when I want to do the same thing with large numbers of sheets. There don't seem to be any issues, but obviously you need to know what is in all the sheets so you you don't inadvertantly overwrite something.

This from the help file;

Select sheets in a workbook
If you select more than one sheet, Microsoft Excel repeats the changes you make to the active sheet on all other selected sheets. These changes may replace data on other sheets.

To select Do this
A single sheet Click the sheet tab.


Two or more adjacent sheets Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet.
Two or more nonadjacent sheets Click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets.
All sheets in a workbook Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

 

[Ivan F Moala]

This can also be done by the commandbutton
[Across Worksheets]...but probably easier doing the way you suggested.

Whilst you would not try and name the sheets array manually in this manner if you ever need to simulate this in code then the quickest is;

<pre/>
Sub CopyAcrossAll()

Worksheets.Select
'// this is where you define what to copy
'// copies to all sheets 123
ActiveCell = 123

End Sub
</pre>

 

[stevebausch]

Ivan and Richard,
Thank you so much for the feedback. I find this ability to be quite clever, just didn't understand it enough to feel comfortable with it.

 

[Tom Urtis]

Steve:

To add to Richard and Ivan's responses - -

The action is usually referred to as "grouping worksheets". As you saw, you can manually group the sheets (and record a macro to do the same), by clicking on a sheet tab and then using the Shift key to group all sheets between and including the two tabs, or Ctrl to group non contiguous sheets.

The code you posted is basically using the Select method of the Worksheets collection in conjunction with the Array function.

There is more than one way to do it VBA-wise. Example, instead of selecting (or typing) the sheet tab names, you can refer to their index numbers in the grouping code:

Worksheets(Array(1, 4, 7)).Select

A popular benefit to grouping sheets is that data and formatting is automatically applied to all grouped sheets at the same time.

You are wise to ask if there are any problems with this practice. Maybe problem is not the right word, but you should be aware that with issues like printer & print area settings, and other sheet level option changes, only the active sheet is affected when grouping via VBA. To do all the sheet level changes (such as print settings) to all the grouped sheets, you must set up a For Each...Next loop to hit each sheet.

Hope this helps.

 

[Ivan F Moala]

On 2002-10-10 03:53, Tom Urtis wrote:
Steve:

To add to Richard and Ivan's responses - -

The action is usually referred to as "grouping worksheets". As you saw, you can manually group the sheets (and record a macro to do the same), by clicking on a sheet tab and then using the Shift key to group all sheets between and including the two tabs, or Ctrl to group non contiguous sheets.

The code you posted is basically using the Select method of the Worksheets collection in conjunction with the Array function.

There is more than one way to do it VBA-wise. Example, instead of selecting (or typing) the sheet tab names, you can refer to their index numbers in the grouping code:

Worksheets(Array(1, 4, 7)).Select

A popular benefit to grouping sheets is that data and formatting is automatically applied to all grouped sheets at the same time.

You are wise to ask if there are any problems with this practice. Maybe problem is not the right word, but you should be aware that with issues like printer & print area settings, and other sheet level option changes, only the active sheet is affected when grouping via VBA. To do all the sheet level changes (such as print settings) to all the grouped sheets, you must set up a For Each...Next loop to hit each sheet.

Hope this helps.

Tom, thats good you pointed this out as the natural inclination would be to set such sheet spicific properties via this method [group] and [set] which won't work as you have pointed out.

 

[PivotMeThis]

Re: Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Select - is

by Tom Urtis: "You are wise to ask if there are any problems with this practice. Maybe problem is not the right word, but you should be aware that with issues like printer & print area settings, and other sheet level option changes, only the active sheet is affected when grouping via VBA. To do all the sheet level changes (such as print settings) to all the grouped sheets, you must set up a For Each...Next loop to hit each sheet."

This is the problem I am having. I have multiple sheets in a workbook that I work on every week - week after week after week...snooze...and I recorded macros to automate some of these repetitive tasks. Formatting changes work great but when I recorded one for page set up, it applies only to the active sheet. I do not understand "for each...next loop" Can someone explain this to me.

I have taken classes and still don't "get" loop, do loop, etc.

 

[pgc01]

Re: Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Select - is

Hi

This code loops through all the worksheets in the active workbook. Is this what you want?

Sub PageSetupAcrossSheets()
Dim wsh As Worksheet
 
For Each wsh In ActiveWorkbook.Worksheets
    With wsh.PageSetup
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Zoom = 100
 
        ' etc.
 
    End With
Next wsh
End Sub

 

[PivotMeThis]

Re: Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Select - is

...maybe...

I only want selected worksheets though. Some sheets are not printed out in my workbook and are in an entirely different format.

 

[pgc01]

Re: Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Select - is

I only want selected worksheets though.

Replace

For Each wsh In ActiveWorkbook.Worksheets

with

For Each wsh In ActiveWindow.SelectedSheets