Can you use worksheet names in macros
I'm just learning to utilise macros and VB in excel and I am having trouble
with the following: Sheet 1 is called "WE 01-01-01" I wish to automate the process of inserting a new worksheet and renaming it "WE 08-01-01" and so on each week. Using a simple macro the insertion process is easy but the renaming part I'm having trouble with. Can anyone help |
Can you use worksheet names in macros
Hi Lyn,
This bit of code below should help you, I think. It adds a new sheet at the end of the workbook, and renames it to whatever you want using an algorithm or some type, or any constant name (in this case, the newly created sheet name is "temp"). Hope that helps. Set NewSheet = Worksheets.Add NewSheet.Name = "Temp" NewSheet.Move After:=Sheets(Sheets.Count) "Lyn" wrote: I'm just learning to utilise macros and VB in excel and I am having trouble with the following: Sheet 1 is called "WE 01-01-01" I wish to automate the process of inserting a new worksheet and renaming it "WE 08-01-01" and so on each week. Using a simple macro the insertion process is easy but the renaming part I'm having trouble with. Can anyone help |
Can you use worksheet names in macros
Thanks Jim, It achieves almost what I need. In the renaming process can I
have the macro use the previous worksheet name eg: "WE 01-01-01" and add 7 to the date to finish up with a new worksheet name "WE 08-01-01"? Or do I need to just use the date format eg: "01-01-01" to finish up with "08-01-01"? Lyn "Jim D." wrote: Hi Lyn, This bit of code below should help you, I think. It adds a new sheet at the end of the workbook, and renames it to whatever you want using an algorithm or some type, or any constant name (in this case, the newly created sheet name is "temp"). Hope that helps. Set NewSheet = Worksheets.Add NewSheet.Name = "Temp" NewSheet.Move After:=Sheets(Sheets.Count) "Lyn" wrote: I'm just learning to utilise macros and VB in excel and I am having trouble with the following: Sheet 1 is called "WE 01-01-01" I wish to automate the process of inserting a new worksheet and renaming it "WE 08-01-01" and so on each week. Using a simple macro the insertion process is easy but the renaming part I'm having trouble with. Can anyone help |
Can you use worksheet names in macros
Lyn
There is probably an easier way of doing this with date formats etc but this seems to work for me: Sub NameSheets() Dim shtDate As Date Dim newSheet As Worksheet Dim shtName As String Set newSheet = Sheets.Add newSheet.Move After:=Sheets(Sheets.Count) shtDate = Right(Sheets(Sheets.Count - 1).Name, 8) shtDate = shtDate + 7 If Day(shtDate) < 10 Then shtName = "WE 0" & Day(shtDate) Else shtName = "WE " & Day(shtDate) End If If Month(shtDate) < 10 Then shtName = shtName & "-0" & Month(shtDate) Else shtName = shtName & "-" & Month(shtDate) End If shtName = shtName & "-" & Right(Year(shtDate), 2) newSheet.Name = shtName End Sub Hope this helps Rowan "Lyn" wrote: Thanks Jim, It achieves almost what I need. In the renaming process can I have the macro use the previous worksheet name eg: "WE 01-01-01" and add 7 to the date to finish up with a new worksheet name "WE 08-01-01"? Or do I need to just use the date format eg: "01-01-01" to finish up with "08-01-01"? Lyn "Jim D." wrote: Hi Lyn, This bit of code below should help you, I think. It adds a new sheet at the end of the workbook, and renames it to whatever you want using an algorithm or some type, or any constant name (in this case, the newly created sheet name is "temp"). Hope that helps. Set NewSheet = Worksheets.Add NewSheet.Name = "Temp" NewSheet.Move After:=Sheets(Sheets.Count) "Lyn" wrote: I'm just learning to utilise macros and VB in excel and I am having trouble with the following: Sheet 1 is called "WE 01-01-01" I wish to automate the process of inserting a new worksheet and renaming it "WE 08-01-01" and so on each week. Using a simple macro the insertion process is easy but the renaming part I'm having trouble with. Can anyone help |
Can you use worksheet names in macros
Thanks Rowan and Jim. I now have exactly what I need.
Cheers Lyn "Rowan" wrote: Lyn There is probably an easier way of doing this with date formats etc but this seems to work for me: Sub NameSheets() Dim shtDate As Date Dim newSheet As Worksheet Dim shtName As String Set newSheet = Sheets.Add newSheet.Move After:=Sheets(Sheets.Count) shtDate = Right(Sheets(Sheets.Count - 1).Name, 8) shtDate = shtDate + 7 If Day(shtDate) < 10 Then shtName = "WE 0" & Day(shtDate) Else shtName = "WE " & Day(shtDate) End If If Month(shtDate) < 10 Then shtName = shtName & "-0" & Month(shtDate) Else shtName = shtName & "-" & Month(shtDate) End If shtName = shtName & "-" & Right(Year(shtDate), 2) newSheet.Name = shtName End Sub Hope this helps Rowan "Lyn" wrote: Thanks Jim, It achieves almost what I need. In the renaming process can I have the macro use the previous worksheet name eg: "WE 01-01-01" and add 7 to the date to finish up with a new worksheet name "WE 08-01-01"? Or do I need to just use the date format eg: "01-01-01" to finish up with "08-01-01"? Lyn "Jim D." wrote: Hi Lyn, This bit of code below should help you, I think. It adds a new sheet at the end of the workbook, and renames it to whatever you want using an algorithm or some type, or any constant name (in this case, the newly created sheet name is "temp"). Hope that helps. Set NewSheet = Worksheets.Add NewSheet.Name = "Temp" NewSheet.Move After:=Sheets(Sheets.Count) "Lyn" wrote: I'm just learning to utilise macros and VB in excel and I am having trouble with the following: Sheet 1 is called "WE 01-01-01" I wish to automate the process of inserting a new worksheet and renaming it "WE 08-01-01" and so on each week. Using a simple macro the insertion process is easy but the renaming part I'm having trouble with. Can anyone help |
All times are GMT +1. The time now is 05:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com