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

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

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

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

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