why doesnt this work

I found this script on the excel forum but i get an error that the following line:

Worksheets(ShtCtr).Name = Worksheets(ShtCtr).Range("B2").Text

is a 1009 error application defined or object defined error

here is the full script:

Option Explicit
Sub RenameSheets()
Dim ShtCtr As Double

For ShtCtr = 1 To Worksheets.Count
Worksheets(ShtCtr).Name = Worksheets(ShtCtr).Range("B2").Text
Next ShtCtr

End Sub


the way i see this vba is that its supposed to extract the text in cell b2 and name the worksheet as such? Please advise on how to correct this.

M.S.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Why have you put it in a loop? Are you trying to rename every sheet the same?

Pete

I found this script on the excel forum but i get an error that the following line:

Worksheets(ShtCtr).Name = Worksheets(ShtCtr).Range("B2").Text

is a 1009 error application defined or object defined error

here is the full script:

Please Login or Register  to view this content.

the way i see this vba is that its supposed to extract the text in cell b2 and name the worksheet as such? Please advise on how to correct this.

M.S.

---------- Post added at 02:46 PM ---------- Previous post was at 02:44 PM ----------

No, i actually didnt notice the loop. I just need to name every sheet based on what a cell i.e. b2 says for each respective sheet. Ideally i would also want it to use only some part of the text in B2 rather than all, but i am a complete beginner in VBA. If you could advise i would be highly thankful

Hi Tom 1977 - I just tried running it and it gave me thesame 1004 error as previously for thesame line. What should i put instead of range B2 (may be thats where my error is)?

Cheers,

Max