ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Can you use worksheet names in macros (https://www.excelbanter.com/excel-programming/332599-can-you-use-worksheet-names-macros.html)

Lyn

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

Jim D.[_2_]

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


Lyn

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


Rowan[_2_]

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


Lyn

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