any way to rename a sheet in VBA?

[dnickelson]

I'm looking to run some queries in VBA and would like the results to come back on a sheet named with a specific part of the query (store number or such). I've searched through the message board but searching for sheet name either returns around 1500 or no matches. Anyone know of a simple example to change a sheet name in VBA. Creating a sheet would work also, may even be better. Not too worried about the format, the simpler the better, I should be able to slip it in where needed.

thanks in advance

Dan

 

[Joe4]

Do you mean some VBA code like:

ActiveSheet.Name = "New Name"

 

[pcc]

If it's the active sheet then use
ActiveSheet.Name = "qwser"

else use

Sheets("Sheet2").Name = "adsf"

... and check out the macro recorder - it's very useful!!

 

[jimboy]

This will rename a sheet;

Sheets("Sheet5").Name = "Myname"

This will add a sheet, then rename it;

Sheets.Add
Sheets(ActiveSheet.Name).Name = "MyNewSheet"

 

[Joe4]

You can also do it by reference, without even knowing the name of sheet currently. For example, if you wanted to rename the 2nd sheet:

Sheets(2).Name = "New Name"

 

[dnickelson]

Okay, I'll try coming up with a 'real' question for you guys sometime. (I'm sure questions like these are good for a laugh sometimes, though)

Thanks for the impressive response on any hand.

Dan

 

[pcc]

just discovered that this work also!

Sheets.Add.Name = "fred"

anything to save time!!!

 

[jimboy]

just discovered that this work also!

Sheets.Add.Name = "fred"

anything to save time!!!

Nice

 

[dnickelson]

how about moving a sheet (to between 2 other specified sheets, to beginning or end, to 7th in line, etc.

A link to some other reference, or some keywords to search for in help would work also.

Thanks again.

 

[jimboy]

Any one of these;

Move first to the end:
Sheets(1).Move after:=Sheets(Sheets.Count)

Move first to after Sheet2 (can change to before):
Sheets(1).Move after:=Sheets("Sheet2")

Move first to after third:
Sheets(1).Move after:=Sheets(3)