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

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

Well-known Member
Joined
May 11, 2002
Messages
810
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

Active Member
Joined
Jun 24, 2002
Messages
341

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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?

Code:
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

Active Member
Joined
Jun 24, 2002
Messages
341
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Re: Sheets(Array("Sheet2", "Sheet3", "Sheet1")).Select - is

I only want selected worksheets though.

Replace

Code:
For Each wsh In ActiveWorkbook.Worksheets


with

Code:
For Each wsh In ActiveWindow.SelectedSheets
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,525
Members
414,245
Latest member
Major Aly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top