Naming and Selecting All Worksheets VBA

[rawrs]

Hi all.

Been at this for 5 hours....I can't find a way to name all my worksheets, then make a variable that would select them all. Speed and cleanliness in the code is very important to me, so any optimal or much better ways would be very appreciated. Here's my code:

Sub meow()    Dim count As Integer
    Dim i As Integer
    
    Dim dNames(1 To 6) As Variant
        items = Worksheets("Item Analysis").Activate
        analysis = Worksheets("Analysis").Activate
        manuf = Worksheets("Make").Activate
        carrier = Worksheets("Carrier Analysis").Activate
        gbCarrier = Worksheets("iPhoneCarrierGB").Activate
        gb = Worksheets("iPhoneGB").Activate
    
    Set all = ActiveWorkbook.Sheets(items, analysis, manuf, carrier, gbCarrier, gb)
    
    
End Sub

 

[mikerickson]

Perhaps

Dim AllSheets as Worksheets

Set AllSheets = ThisWorkbook.Sheets(Array("Item Analysis", "Analysis", "Make", "Carrier Analysis", "iPhoneCarrierGB", "iPhoneGB")

What do you want to do with these sheets once you have them in one Worksheets object?

 

[rawrs]

They all have the same layout. I want to apply changes and adjustments to all of them.

Also, i get a "Type Mismatch" error with that.

 

[veyselemre]

Dim AllSheets As Sheets
Set AllSheets = Sheets(Array("Sayfa2", "Sayfa3"))
AllSheets.Delete

 

[rawrs]

Thank you very much. any idea how to select a range with that? I can't select it on mine.

 

[veyselemre]

    Dim AllSheets As Sheets
    Dim sh As Worksheet
    Set AllSheets = Sheets(Array("Sayfa3", "Sayfa2"))
    AllSheets.Select
    Range("A1:K1").Select
    Selection.Interior.Color = vbYellow

 

[mikerickson]

Or perhaps

Dim AllSheets As Sheets

Set AllSheets = ThisWorkbook.Sheets(Array("sheet1", "sheet2", "Sheet3"))
With AllSheets
    .Item(1).Range("A1:A10").Interior.ColorIndex = 6
    .FillAcrossSheets .Item(1).Range("A1:A10"), xlFillWithFormats
End With

 

[rawrs]

Or perhaps

Dim AllSheets As Sheets

Set AllSheets = ThisWorkbook.Sheets(Array("sheet1", "sheet2", "Sheet3"))
With AllSheets
    .Item(1).Range("A1:A10").Interior.ColorIndex = 6
    .FillAcrossSheets .Item(1).Range("A1:A10"), xlFillWithFormats
End With

None of this works. I don't understand why it's working in your VBA editor and not mine. It doesn't seem possible that it works in yours and not in mine. This is so simple and vba is making it so hard

 

[mikerickson]

do the sheet names match what your workbook has?

 

[rawrs]

Or perhaps

Dim AllSheets As Sheets

Set AllSheets = ThisWorkbook.Sheets(Array("sheet1", "sheet2", "Sheet3"))
With AllSheets
    .Item(1).Range("A1:A10").Interior.ColorIndex = 6
    .FillAcrossSheets .Item(1).Range("A1:A10"), xlFillWithFormats
End With

taking out the .filacrosssheets part of your code, this only works on 1 page because you have to select a range for .Item(range).

Why can't I just add these to all of my sheets?