Variable to rename a tab in a worksheet via VBA

[Colin Hayes]

Hi

I'm trying to rename a worksheet tab via VBA.

My problem is that the tab has a changing name.

EG

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "File 1"


Sheet1 in this case will change in name next time I want to run the
macro , so it will fail.

How can I build in a variable for the sheet selection so I can always
rename it 'File 1' , whatever it used to be. It's always and only the
first sheet I'm applying this to , by the way.

Grateful for any help.

 

[Living the Dream]

Hi

Rather than using the Sheets(Tab.Name), use the actual (Sheet.Name)
which is what you see when using the VBE: Sheet1 (Sheet1), in your case
it would look like this Sheet1 (File_1).

I found this code from
http://www.techrepublic.com/blog/10...ence-excel-workbooks-and-sheets-using-vba/967
which may point you in the right direction.

9: Reference sheets by index

Index values come in handy when you don’t care about specific sheets,
but only their number or order. Granted, that’s not going to be a common
task, but occasionally, referencing by index values can come in handy.
The following procedure adds and deletes sheets based on the number of
sheets you want:

Function ControlSheetNumber(intSheets As Integer)

'Add or delete sheets to equal intSheets.

Application.DisplayAlerts = False

'Delete sheets if necessary

While Worksheets.Count > intSheets

Worksheets(1).Delete

Wend

'Add sheets if necessary

While Worksheets.Count < intSheets

Worksheets.Add

Wend

Application.DisplayAlerts = True

End Function

Use caution when executing this function because it deletes the first
Sheet object in the collection, even if that sheet contains content. It
simply adds and deletes sheets, depending on the value you pass. This
function is useful when creating new workbooks programmatically.

HTH
Mick.

 

[plinius]

Il 04/08/2012 05:26, Colin Hayes ha scritto:

Hi

I'm trying to rename a worksheet tab via VBA.

My problem is that the tab has a changing name.

EG

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "File 1"


Sheet1 in this case will change in name next time I want to run the
macro , so it will fail.

How can I build in a variable for the sheet selection so I can always
rename it 'File 1' , whatever it used to be. It's always and only the
first sheet I'm applying this to , by the way.

Grateful for any help.

Use
Sheet1.name = "File 1"

Hi,
E.

 

[Colin Hayes]

Il 04/08/2012 05:26, Colin Hayes ha scritto:

Use
Sheet1.name = "File 1"

Hi,
E.

Hi

OK Thanks for this.

I'm not sure how to implement the line you suggest. I added it as a
single line to my macro , and it has no effect unfortunately.

Can you advise if I need more than the single line to rename my
worksheet?


Thanks.

 

[plinius]

Il 04/08/2012 13:27, Colin Hayes ha scritto:

Hi

OK Thanks for this.

I'm not sure how to implement the line you suggest. I added it as a
single line to my macro , and it has no effect unfortunately.

Can you advise if I need more than the single line to rename my worksheet?


Thanks.

That single line change the property "name" of the sheet "Sheet1"
You can try it in the immediate windows.
It is equivalent to
Sheets(1).name = "File 1"
and you can use also this type of code that recognize the sheet by his
number.

E.

 

[Colin Hayes]

That single line change the property "name" of the sheet "Sheet1"
You can try it in the immediate windows.
It is equivalent to
Sheets(1).name = "File 1"
and you can use also this type of code that recognize the sheet by his number.

E.

HI

OK thanks for getting back.

I entered the line to my macro and it worked perfectly,

Thanks you for your help.

^_^

Best Wishes