CODEWITHSUNDEEP
vbaexcel
Mario
Mario

Reputation: 177

VBA Excel: Replacing all sheets .CodeName value with .Name value

I want to change all sheets' .codename value in VBA to their corresponding .name values. I am trying the below mentioned code but its not working. Kindly advise

Dim Ws As Variant
For k = 1 To 37
ThisWorkbook.VBProject.VBComponents(Ws(k)).CodeName = Ws(k).Name
Next k

Upvotes: 0

Views: 196

Answers (1)

quantum285
quantum285

Reputation: 1032

Firstly you have to head onto Macro Security, on my version of Excel this can be found on the Developer tab.
Then tick the box that says "Trust access to the VBA project object model".
The following code should then allow you to achieve what you want: 

Sub test()
For Each ws In ThisWorkbook.Sheets
    ThisWorkbook.VBProject.VBComponents(ws.CodeName).Name = ws.Name
Next ws
End Sub

Upvotes: 1

Related Questions