What's wrong with my VBA code to rename sheets?

[ORoxo]

Can any of you help me figuring out what's wrong with the code below?

Sub Macro()
Dim w As Worksheet
For Each w In Worksheets


If w.Name = "indkey" Or _
w.Name = "subdivkey" Or _
w.Name = "Q2" Or _
w.Name = "Q5" Or _
w.Name = "data_ps3" Then




Else


Worksheet.Name = Range("Z2")


End If


Next


End Sub

Thanks

 

[Fluff]

Try

w.Name = w.Range("Z2")

 

[Vbagreenhorn1]

you are using w so it must be w.name

 

[cerfani]

the worksheet object you are using 'Worksheet' does not exist it appears. Perhaps you meant what Fluff said?

Also Im curious, are you working on something cfw related for ps3?

 

[ORoxo]

You were right, stupid mistake

Now it's giving me this error:

Code as follows:

Sub Macro()Dim w As Worksheet


If w.Name = "indkey" Or _
w.Name = "subdivkey" Or _
w.Name = "Q2" Or _
w.Name = "Q5" Or _
w.Name = "data_ps3" Then




Else


For Each w In Worksheets


w.Name = Range("Z2")


Next


End If


End Sub

 

[ORoxo]

the worksheet object you are using 'Worksheet' does not exist it appears. Perhaps you meant what Fluff said?

Also Im curious, are you working on something cfw related for ps3?

No, not at all. Sorry to disappoint

 

[cerfani]

you should reference the specific worksheet that Z2 is on, maybe the active worksheet is something else?? You have to put a breakpoint in before that line and check the values of variables, etc

 

[Fluff]

It should be

Sub Macro()
   Dim w As Worksheet
   
   For Each w In Worksheets
      If w.Name = "indkey" Or _
         w.Name = "subdivkey" Or _
         w.Name = "Q2" Or _
         w.Name = "Q5" Or _
         w.Name = "data_ps3" Then
      Else
         w.Name = w.Range("Z2")
      End If
   Next
End Sub

 

[cerfani]

I just used to have a jailbroken ps3, and those string values seemed familiar. From the file structure of games or something. I was just wondering.

 

[ORoxo]

It should be

Sub Macro()
   Dim w As Worksheet
   
   For Each w In Worksheets
      If w.Name = "indkey" Or _
         w.Name = "subdivkey" Or _
         w.Name = "Q2" Or _
         w.Name = "Q5" Or _
         w.Name = "data_ps3" Then
      Else
         w.Name = w.Range("Z2")
      End If
   Next
End Sub

That worked perfecly, Fluff

Understood what I was doing wrong, thanks!