Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable to rename a tab in a worksheet via VBA
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable to rename a tab in a worksheet via VBA
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/10t...-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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable to rename a tab in a worksheet via VBA
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable to rename a tab in a worksheet via VBA
In article , plinius
writes 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. 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable to rename a tab in a worksheet via VBA
Il 04/08/2012 13:27, Colin Hayes ha scritto:
In article , plinius writes 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. 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable to rename a tab in a worksheet via VBA
In article , plinius
writes 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for variable worksheet name and rename | Excel Programming | |||
rename the new worksheet | Excel Programming | |||
Rename files with variable names | Excel Programming | |||
Using information from one worksheet, to rename inserted worksheet | Excel Worksheet Functions | |||
VBA: rename a variable | Excel Programming |