Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Tab Name from Cell C4
I have a worksheet that I would like to have the Tab automatically rename
itself to whatever value is in Cell C4. This worksheet will be copied (via copy worksheet macro) multiple times (move to end with create a copy option checked). I have never used VBA in Excel so I am not sure how to make this work. I tried using what you had suggested for Wally but couldn't make that work for me. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Tab Name from Cell C4
In the VBEditor, put the following in the worksheet to be copied.
Everytime you go to that worksheet, the macro will fire and change the worksheet's name to whatever is in cell C4 of that worksheet. If the name is illegal, the worksheet will not change the name. Private Sub Worksheet_Activate() On Error Resume Next ActiveSheet.Name = Range("C4").Value End Sub -- Hope this helps. If this post was helpfull, please remember to click on the ''YES'' button at the bottom of the screen. Thanks, Gary Brown "JAK" wrote: I have a worksheet that I would like to have the Tab automatically rename itself to whatever value is in Cell C4. This worksheet will be copied (via copy worksheet macro) multiple times (move to end with create a copy option checked). I have never used VBA in Excel so I am not sure how to make this work. I tried using what you had suggested for Wally but couldn't make that work for me. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Tab Name from Cell C4
If you are new to macros, this link to Jon Peltier's site may help you
implement Gary's code: http://peltiertech.com/WordPress/200...e-elses-macro/ Hope this helps, Hutch "JAK" wrote: I have a worksheet that I would like to have the Tab automatically rename itself to whatever value is in Cell C4. This worksheet will be copied (via copy worksheet macro) multiple times (move to end with create a copy option checked). I have never used VBA in Excel so I am not sure how to make this work. I tried using what you had suggested for Wally but couldn't make that work for me. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Tab Name from Cell C4
Gary,
Thank you so much, your very simple code and clear instructions worked very well! I do have one follow up question however. I currently have a macro to copy the original worksheet named 'server'. If I change the name of the worksheet tab with this code it breaks the macro to copy. Can you tell me how to make "Server" a variable for the current worksheet. Also, is there a way to tell it to copy to the end instead of after "Sheet (2)" Sub CopySheet() ' ' CopySheet Macro ' Macro recorded 6/23/2008 by Jason A. Koller ' ' Sheets("Server").Select Sheets("Server").Copy After:=Sheets(2) End Sub Thanks again Gary... and Tom for the link to learn about how to use someone elses macro's! Jason "Gary Brown" wrote: In the VBEditor, put the following in the worksheet to be copied. Everytime you go to that worksheet, the macro will fire and change the worksheet's name to whatever is in cell C4 of that worksheet. If the name is illegal, the worksheet will not change the name. Private Sub Worksheet_Activate() On Error Resume Next ActiveSheet.Name = Range("C4").Value End Sub -- Hope this helps. If this post was helpfull, please remember to click on the ''YES'' button at the bottom of the screen. Thanks, Gary Brown "JAK" wrote: I have a worksheet that I would like to have the Tab automatically rename itself to whatever value is in Cell C4. This worksheet will be copied (via copy worksheet macro) multiple times (move to end with create a copy option checked). I have never used VBA in Excel so I am not sure how to make this work. I tried using what you had suggested for Wally but couldn't make that work for me. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Tab Name from Cell C4
Here is one way...
Sub CopySheet() 'Copies the active sheet after all other sheets. ActiveSheet.Copy After:=Worksheets(Worksheets.Count) End Sub The code above will create a copy of the active worksheet. The new worksheet will be after all the other worksheets. Copy this macro into a VBA module in your workbook. Make sure the 'Server' sheet is the active sheet when you run it (make sure it is the sheet that is showing, and click a cell on it to make sure). I wrote it to use the active sheet because it sounds like this sheet starts out as 'Server' but its name may get changed via Gary's event code. Hope this helps, Hutch "JAK" wrote: Gary, Thank you so much, your very simple code and clear instructions worked very well! I do have one follow up question however. I currently have a macro to copy the original worksheet named 'server'. If I change the name of the worksheet tab with this code it breaks the macro to copy. Can you tell me how to make "Server" a variable for the current worksheet. Also, is there a way to tell it to copy to the end instead of after "Sheet (2)" Sub CopySheet() ' ' CopySheet Macro ' Macro recorded 6/23/2008 by Jason A. Koller ' ' Sheets("Server").Select Sheets("Server").Copy After:=Sheets(2) End Sub Thanks again Gary... and Tom for the link to learn about how to use someone elses macro's! Jason "Gary Brown" wrote: In the VBEditor, put the following in the worksheet to be copied. Everytime you go to that worksheet, the macro will fire and change the worksheet's name to whatever is in cell C4 of that worksheet. If the name is illegal, the worksheet will not change the name. Private Sub Worksheet_Activate() On Error Resume Next ActiveSheet.Name = Range("C4").Value End Sub -- Hope this helps. If this post was helpfull, please remember to click on the ''YES'' button at the bottom of the screen. Thanks, Gary Brown "JAK" wrote: I have a worksheet that I would like to have the Tab automatically rename itself to whatever value is in Cell C4. This worksheet will be copied (via copy worksheet macro) multiple times (move to end with create a copy option checked). I have never used VBA in Excel so I am not sure how to make this work. I tried using what you had suggested for Wally but couldn't make that work for me. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet Tab Name from Cell C4
Hutch,
Your assumption of what my request is spot on, that worked exactly as I hoped it would. I appreciate you and Gary sharing your knowledge and expertise. Best regards, Jason "Tom Hutchins" wrote: Here is one way... Sub CopySheet() 'Copies the active sheet after all other sheets. ActiveSheet.Copy After:=Worksheets(Worksheets.Count) End Sub The code above will create a copy of the active worksheet. The new worksheet will be after all the other worksheets. Copy this macro into a VBA module in your workbook. Make sure the 'Server' sheet is the active sheet when you run it (make sure it is the sheet that is showing, and click a cell on it to make sure). I wrote it to use the active sheet because it sounds like this sheet starts out as 'Server' but its name may get changed via Gary's event code. Hope this helps, Hutch "JAK" wrote: Gary, Thank you so much, your very simple code and clear instructions worked very well! I do have one follow up question however. I currently have a macro to copy the original worksheet named 'server'. If I change the name of the worksheet tab with this code it breaks the macro to copy. Can you tell me how to make "Server" a variable for the current worksheet. Also, is there a way to tell it to copy to the end instead of after "Sheet (2)" Sub CopySheet() ' ' CopySheet Macro ' Macro recorded 6/23/2008 by Jason A. Koller ' ' Sheets("Server").Select Sheets("Server").Copy After:=Sheets(2) End Sub Thanks again Gary... and Tom for the link to learn about how to use someone elses macro's! Jason "Gary Brown" wrote: In the VBEditor, put the following in the worksheet to be copied. Everytime you go to that worksheet, the macro will fire and change the worksheet's name to whatever is in cell C4 of that worksheet. If the name is illegal, the worksheet will not change the name. Private Sub Worksheet_Activate() On Error Resume Next ActiveSheet.Name = Range("C4").Value End Sub -- Hope this helps. If this post was helpfull, please remember to click on the ''YES'' button at the bottom of the screen. Thanks, Gary Brown "JAK" wrote: I have a worksheet that I would like to have the Tab automatically rename itself to whatever value is in Cell C4. This worksheet will be copied (via copy worksheet macro) multiple times (move to end with create a copy option checked). I have never used VBA in Excel so I am not sure how to make this work. I tried using what you had suggested for Wally but couldn't make that work for me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I click on a cell on one worksheet and make it take me to another worksheet | New Users to Excel | |||
If in specific cell in any worksheet but worksheet one add to spec | Excel Worksheet Functions | |||
copy data in a cell from worksheet A to worksheet B | Excel Discussion (Misc queries) | |||
How do I copy a date in a worksheet cell to another worksheet? | Excel Worksheet Functions | |||
How can I link cell colours from worksheet to worksheet/workbook? | Excel Worksheet Functions |