Rename Worksheet Code

guerilla · Dec 12th 2006

Hi, I need a little help with my macro. I currently download a report and it has a different worksheet name everytime. Is there any way using a macro to change the worksheet name? (My macro extracts data from worksheet a and copies into worksheet b) Hope I've provided enough info. Thanks for all replies

ravenswood · Dec 12th 2006

Re: Rename A Worksheet

If the worksheet is always in the same place, you can reference it by its index number.

Code

ThisWorkbook.Sheets(1)

PeterLar · Dec 12th 2006

Re: Rename A Worksheet

If you wish to change Sheet1's name, try

Code

ThisWorkbook.Sheets("Sheet1").Name = "Your new name"

Dave Hawley · Dec 13th 2006

Re: Rename A Worksheet

See Also Add Worksheets to Excel via VBA and Reference Sheets in Excel

guerilla · Dec 13th 2006

Re: Rename Worksheet Code

thanks for the replies, I'm a bit of a newcomer to macros so please bear with me, at the moment my script is looking like this

Code

Sheets("old report name that is different everytime").Select
         Sheets("old report name that is different everytime").Name = "new report name"

What I'm trying to do is have my macro change the worksheet name to a generic name, i.e. report so that the rest of the macro will work. thanks again

Dave Hawley · Dec 13th 2006

Re: Rename Worksheet Code

Read the 2nd link I posted. You use the CodeName so any name change etc has no adverse effect.

guerilla · Dec 13th 2006

Re: Rename Worksheet Code

thanks for the quick reply, i really am a beginner here and I'm still having trouble getting this to work.

Code

ThisWorkbook.Sheets(1) //from 2nd post to select the sheet
ThisWorkbook.Sheets("Sheet1").Name = "Your new name" //from 3rd post to rename this sheet

on the first line should i include .select at the end?

guerilla · Dec 13th 2006

Re: Rename Worksheet Code

ok, no problem, all sorted now, thanks for all the help.

Rename Worksheet Code

Participate now!

Share