copy worksheet and automatically name

[Pennyc]

I need to generate multiple copies of one worksheet within a workbook and
automatically name each of those new worksheets (sequentially) based on a
list of numbers on another sheet in the same workbook. Does anyone know of a
way to do this?

Thanks!

 

[JLatham]

Pennyc, The code below will let you define which sheet is to be copied, and
which sheet has the number list on it and what cells contain that list. Edit
those values appropriately and just run the macro to make the copies.
Remember that your number list must contain unique numbers, no worksheet can
be renamed the same as one that already exists in a workbook. The code
guards against that type of error.

To put the code to work:
Open your workbook, press [Alt]+[F11] to open the VB Editor and use Insert
--> Module to create a code module. Copy the code below and paste it into
that module and then edit it for your workbook. Run it from Tools --> Macro
--> Macros

Sub MakeSheetCopies()
Const SheetToCopy = "SheetToCopy" ' name of sheet
Const ListSheetName = "SheetWithList" ' name of sheet
Const startOfList = "A2" ' first of list's address
Const endOfList = "A7" ' end of list's address

Dim copySheet As Worksheet
Dim numberList As Range
Dim anyNumber As Range

Set copySheet = ThisWorkbook.Worksheets(SheetToCopy)
Set numberList = ThisWorkbook.Worksheets(ListSheetName). _
Range(startOfList & ":" & endOfList)

For Each anyNumber In numberList
copySheet.Copy After:=Sheets(Worksheets.Count)
On Error Resume Next
'if sheet name already exists, will generate an
'error that we must deal with
ActiveSheet.Name = SheetToCopy & "-" & anyNumber
If Err <> 0 Then
Err.Clear
MsgBox "Could not rename " & ActiveSheet.Name
End If
On Error GoTo 0
Next
'good housekeeping
Set copySheet = Nothing
Set numberList = Nothing
End Sub

 

[Don Guillett]

A simple one assuming sheet names not duplicated
Sub addsheetsandname()
For Each c In Sheets("sheet2").Range("a1:a3")
Sheets("sheet1").Copy after:=ActiveSheet
ActiveSheet.Name = c
Next c
End Sub

 

[Pennyc]

Thanks for the response! This worked beatufully! The only change I made was
to modify the "ActiveSheet.Name" line to say "= anyNumber". I only wanted
the actual number in the sheet name.

However I'm running into another issue. There is a cell on the sheets I
created that uses the following formula to put the sheet name in the cell:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,7)

I am then doing several VLookups on the sheet using the value in that cell
(i.e., cell name) to look up values in a list. The first column of that
lookup list is the list of numbers we used to generate the sheet names in the
first place.

I've tried a few different things to fix this, such as formatting the cell
and the list of numbers in the same way but nothing will work for me.

Can anyone help with this?

Thanks again!

Pennyc, The code below will let you define which sheet is to be copied, and
which sheet has the number list on it and what cells contain that list. Edit
those values appropriately and just run the macro to make the copies.
Remember that your number list must contain unique numbers, no worksheet can
be renamed the same as one that already exists in a workbook. The code
guards against that type of error.

To put the code to work:
Open your workbook, press [Alt]+[F11] to open the VB Editor and use Insert
--> Module to create a code module. Copy the code below and paste it into
that module and then edit it for your workbook. Run it from Tools --> Macro
--> Macros

Sub MakeSheetCopies()
Const SheetToCopy = "SheetToCopy" ' name of sheet
Const ListSheetName = "SheetWithList" ' name of sheet
Const startOfList = "A2" ' first of list's address
Const endOfList = "A7" ' end of list's address

Dim copySheet As Worksheet
Dim numberList As Range
Dim anyNumber As Range

Set copySheet = ThisWorkbook.Worksheets(SheetToCopy)
Set numberList = ThisWorkbook.Worksheets(ListSheetName). _
Range(startOfList & ":" & endOfList)

For Each anyNumber In numberList
copySheet.Copy After:=Sheets(Worksheets.Count)
On Error Resume Next
'if sheet name already exists, will generate an
'error that we must deal with
ActiveSheet.Name = SheetToCopy & "-" & anyNumber
If Err <> 0 Then
Err.Clear
MsgBox "Could not rename " & ActiveSheet.Name
End If
On Error GoTo 0
Next
'good housekeeping
Set copySheet = Nothing
Set numberList = Nothing
End Sub

I need to generate multiple copies of one worksheet within a workbook and
automatically name each of those new worksheets (sequentially) based on a
list of numbers on another sheet in the same workbook. Does anyone know of a
way to do this?

Thanks!

 

[Pennyc]

I should add that the problem seems to be that the MID function is returning
text, but the vlookup will only work if that cell contains a numeric value,
since the lookup list has numeric values in the left-most column. These is
the same list of numbers that were used to create the sheet names in the
first place.

Can anyone help with this problem? I've searched past discussions and can't
find anything that does the trick.

thanks!

Thanks for the response! This worked beatufully! The only change I made was
to modify the "ActiveSheet.Name" line to say "= anyNumber". I only wanted
the actual number in the sheet name.

However I'm running into another issue. There is a cell on the sheets I
created that uses the following formula to put the sheet name in the cell:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,7)

I am then doing several VLookups on the sheet using the value in that cell
(i.e., cell name) to look up values in a list. The first column of that
lookup list is the list of numbers we used to generate the sheet names in the
first place.

I've tried a few different things to fix this, such as formatting the cell
and the list of numbers in the same way but nothing will work for me.

Can anyone help with this?

Thanks again!

Pennyc, The code below will let you define which sheet is to be copied, and
which sheet has the number list on it and what cells contain that list. Edit
those values appropriately and just run the macro to make the copies.
Remember that your number list must contain unique numbers, no worksheet can
be renamed the same as one that already exists in a workbook. The code
guards against that type of error.

To put the code to work:
Open your workbook, press [Alt]+[F11] to open the VB Editor and use Insert
--> Module to create a code module. Copy the code below and paste it into
that module and then edit it for your workbook. Run it from Tools --> Macro
--> Macros

Sub MakeSheetCopies()
Const SheetToCopy = "SheetToCopy" ' name of sheet
Const ListSheetName = "SheetWithList" ' name of sheet
Const startOfList = "A2" ' first of list's address
Const endOfList = "A7" ' end of list's address

Dim copySheet As Worksheet
Dim numberList As Range
Dim anyNumber As Range

Set copySheet = ThisWorkbook.Worksheets(SheetToCopy)
Set numberList = ThisWorkbook.Worksheets(ListSheetName). _
Range(startOfList & ":" & endOfList)

For Each anyNumber In numberList
copySheet.Copy After:=Sheets(Worksheets.Count)
On Error Resume Next
'if sheet name already exists, will generate an
'error that we must deal with
ActiveSheet.Name = SheetToCopy & "-" & anyNumber
If Err <> 0 Then
Err.Clear
MsgBox "Could not rename " & ActiveSheet.Name
End If
On Error GoTo 0
Next
'good housekeeping
Set copySheet = Nothing
Set numberList = Nothing
End Sub

I need to generate multiple copies of one worksheet within a workbook and
automatically name each of those new worksheets (sequentially) based on a
list of numbers on another sheet in the same workbook. Does anyone know of a
way to do this?

Thanks!