loop changing sheet names based on cell value

[288enzo]

Hi all,

I've found how to change a sheet name based on a cell value.

Sheets("Sheet1").name = Range("A1").Value

I would like to add this to a loop I have. Can it even be done?
This is what I came up with, epic fail!

Dim Sht as Variant
    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        With Sheets(Sht)
        .Sheets.Name = .Range("A2").Value
        End With
    Next

 

[GWteB]

The fact that you want to do this in a loop suggests that you want to rename multiple worksheets. Is that correct?

 

[288enzo]

The fact that you want to do this in a loop suggests that you want to rename multiple worksheets. Is that correct?

Yes, that is correct. I'm looking to change Sheet1, Sheet2, and Sheet3 to the value in A2 on either of their respective sheets.

 

[GWteB]

then you were pretty close...

Dim Sht as Variant
    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        With Sheets(Sht)
            .Name = .Range("A2").Value
        End With
    Next

 

[288enzo]

then you were pretty close...

Dim Sht as Variant
    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        With Sheets(Sht)
            .Name = .Range("A2").Value
        End With
    Next

I was so close. So I can speak more intelligently, what is ".Name" called? I would like to know more about the different <insert name here> that can be used with a "With" statement. I would like to start with the basics.
Ones I know from other questions I posted are -
.Range
.Columns
.Rows

Thank you!

 

[GWteB]

Well, the VBA Editor (VBE) can help you with that. The Object Browser (F2 key) is a nice tool, and taking advantage of Intellisense may also be a great help.