Can I use "Sheets(Array(" with worksheet variables instead of names? 2

[VBAjedi]

I've used "Sheets(Array(" in the past to manipulate multiple sheets at once. I.e.:

Sheets(Array("Sheet1", "Sheet2").Select

I'd like to be able to do something similar with worksheet variables:

Dim Sh3110 As Worksheet
Set Sh3110 = "Worksheet Name"
...
Sheets(Array(Sh3110, Sh3160, Sh3180, Sh3190)).Cells.Clear
Sheets(Array(Sh3110, Sh3160, Sh3180, Sh3190)).Cells.(1,1).Value = "Some string"

The above code gives me a "Type Mismatch" error...

Thanks in advance!

VBAjedi

 

[VBAjedi]

I'd still like to figure out how to clear or write to multiple sheets at once (for execution speed purposes)... in the meantime, I figured out that you can create an array and loop through it one at a time:

Dim sht As Worksheet
For Each sht In Sheets(Array(Sh3110.Name, Sh3160.Name, Sh3180.Name, Sh3190.Name))
  sht.Cells.Clear
  sht.Cells(1, 1).Value = "Bing!!!"
Next sht

VBAjedi

 

[SkipVought]

him

NO!

Dim sht As Worksheet

For Each sht In thisworkbook.worksheets
   with sht
      select case .name
         case "Sheet1","Sheet2" '[b]LIST ALL SHEETS[/b]
            .Cells.Clear  
            .Cells(1, 1).Value = "Bing!!!" 
      end select 
   end with
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

You can set values/formulas for specific sheet and next copy it to all sheets in Sheets(Array(...)) using FillAcrossSheets method, the template sheet has to be in the Array(...) and range pointed as source.


combo

 

[VBAjedi]

Ooohh... a new toy! Never played with FillAcrossSheets before. ;-) Have a star...

I've already got a variant on what Skip suggested working, but I'll have to try out FillAcrossSheets. Thanks for the tip!

VBAjedi