Group VBA defined Sheets

[G12]

Hi.

I cannot get my head round the following.

I have defined a couple of sheets in VBA so that:

        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.

 Sheets(Array("FirstSheet", "SecondSheet", "ThirdSheet")).Select

and it you wanted to insert a value in all of the C5 cells this would be

    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

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

 

[Richard Schollar]

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:

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

 

[jim may]

This example worked for me. Change sample Cell references to suit your needs

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]

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

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]

Excellent.

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

All the best

G12

 

[bcopesetic]

Hi.

I cannot get my head round the following.

I have defined a couple of sheets in VBA so that:

        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.

 Sheets(Array("FirstSheet", "SecondSheet", "ThirdSheet")).Select

and it you wanted to insert a value in all of the C5 cells this would be

    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

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