Group VBA defined Sheets

G12

Board Regular
Joined
Nov 11, 2008
Messages
113
Office Version
  1. 365
Platform
  1. Windows
Hi.

I cannot get my head round the following.

I have defined a couple of sheets in VBA so that:
Code:
        Set wsOne = Worksheets("FirstSheet")
        Set wsTwo = Worksheets("SecondSheet")
        Set wsThree = Worksheets("ThirdSheet")
Now using the recorder I can get that if you were to group the sheets it would come up with something like this.
Code:
 Sheets(Array("FirstSheet", "SecondSheet", "ThirdSheet")).Select
and it you wanted to insert a value in all of the C5 cells this would be
Code:
    Sheets(Array("FirstSheet", "SecondSheet", "ThirdSheet")).Select
    Sheets("FirstSheet").Activate
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[5]C:R[396]C)"

But how can I do this using my defined names.

Ideally I would like to be able to do somehting like this

Code:
Sheets(Array(wsOne, wsTwo,wsThree)).Range("C5:K5").value = "=SUBTOTAL(9,R[5]C:R[396]C)"

But this is wrong and I don't know how to sort it.

Thanks in advance

G12
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Being able to write to multiple sheets at once is a byproduct of selecting them, so I am afraid there is no shortcut way beyond what the macro recorder provides (unless you specifically loop thru them in code, as I'm sure you can already do).

If you want to select multiple sheets using your object references, you need to convert your object references into either their sheet names (as strings) or sheet indices:

Code:
Sheets(Array(wsOne.Name, wsTwo.Index,wsThree.Name)).Select
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
This example worked for me. Change sample Cell references to suit your needs

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/14/2009 by Jmay
'

'
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    For Each Sh In Windows(1).SelectedSheets
    Sh.Range("C5").FormulaR1C1 = "=SUBTOTAL(9,R[5]C:R[9]C)"
    Next Sh
    Sheets("Sheet1").Select
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

Another option, that avoids selecting the sheets, is to set your values/formulas/formats in the first worksheet and then replicate it to the others. Ex.:

Code:
Sub a()
Dim wsOne As Worksheet, wsTwo As Worksheet, wsThree As Worksheet
Dim MySheets As Sheets
 
' Define the objects
Set wsOne = Worksheets("FirstSheet")
Set wsTwo = Worksheets("SecondSheet")
Set wsThree = Worksheets("ThirdSheet")
Set MySheets = Sheets(Array(wsOne.Name, wsTwo.Name, wsThree.Name))
 
' Set the values in FirstSheet and replicate in the others
wsOne.Range("C5") = "=SUBTOTAL(9,R[5]C:R[396]C)"
MySheets.FillAcrossSheets wsOne.Range("C5")
 
wsOne.Range("F1:G1") = Array("Name", "Address")
MySheets.FillAcrossSheets wsOne.Range("F1:G1")
 
' ...
End Sub

Remark: in this example you don't need the wsTwo and wsThree objects, I just declared them assuming you'd use them for some other thing.
 

G12

Board Regular
Joined
Nov 11, 2008
Messages
113
Office Version
  1. 365
Platform
  1. Windows
Excellent.

This more than answers my question. Thanks all for letting me know the score and providing the options.

All the best

G12
 

bcopesetic

New Member
Joined
May 24, 2016
Messages
1
Hi.

I cannot get my head round the following.

I have defined a couple of sheets in VBA so that:
Code:
        Set wsOne = Worksheets("FirstSheet")
        Set wsTwo = Worksheets("SecondSheet")
        Set wsThree = Worksheets("ThirdSheet")
Now using the recorder I can get that if you were to group the sheets it would come up with something like this.
Code:
 Sheets(Array("FirstSheet", "SecondSheet", "ThirdSheet")).Select
and it you wanted to insert a value in all of the C5 cells this would be
Code:
    Sheets(Array("FirstSheet", "SecondSheet", "ThirdSheet")).Select
    Sheets("FirstSheet").Activate
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[5]C:R[396]C)"

But how can I do this using my defined names.

Ideally I would like to be able to do somehting like this

Code:
Sheets(Array(wsOne, wsTwo,wsThree)).Range("C5:K5").value = "=SUBTOTAL(9,R[5]C:R[396]C)"

But this is wrong and I don't know how to sort it.

Thanks in advance

G12

I realize this is a fairly old thread but to simplify your code you do not need to list the sheet names in an array. You can list the sheet by number (in the order it appears), see below. If you use the number it is the order that it appears in the excel document. Then if the tab name is changed it will not affect your group.

I use the below to update multiple sheets that are used to provide different views of reporting. I added a button to group them, then I make my change and then I added a button to ungroup them (which is Sheet1.Select)

Sheets(Array(1,2,3)).Select
Sheet1.Activate
 

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

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