I've seen a number of posts that do most of what I need, but not exactly what i'm looking for.
I'd like to run the VBA on an active worksheet
The VBA would copy the active work sheet and duplicate it and many times as there are names in a column on another spreadsheet.
Worksheet "DATA", Cells A5:A34 contain the names I want to use
Often the 1st cell has the name of the active worksheet so I want to test for that and make sure I don't duplicate it.
I want the duplicate work sheets to appear after the active worksheet.
I've placed the following code on the "DATA" Worksheet:
Private Sub CommandButton1_Click() Call CreateWorksheets(Sheets("DATA").Range("A5:A34")) End Sub Sub CreateWorksheets(Names_Of_Sheets As Range) Dim No_Of_Sheets_to_be_Added As Integer Dim Sheet_Name As String Dim i As Integer No_Of_Sheets_to_be_Added = Names_Of_Sheets.Rows.Count For i = 1 To No_Of_Sheets_to_be_Added Sheet_Name = Names_Of_Sheets.Cells(i, 1).Value 'Only add sheet if it doesn't exist already and the name is longer than zero characters If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then 'Worksheets.Add().Name = Sheet_Name ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count) End If Next i End Sub
I've placed the following code in a module:
Function Sheet_Exists(WorkSheet_Name As String) As Boolean Dim Work_sheet As Worksheet Sheet_Exists = False For Each Work_sheet In ThisWorkbook.Worksheets If Work_sheet.Name = WorkSheet_Name Then Sheet_Exists = True End If Next End Function
The line "Worksheets.Add().Name=Sheet-Name", adds new work sheets but doesn't copy the active worksheet
The Line "ActiveSheet.Copy After :=ActiveWorkbook.Sheets(Worksheets.Count)" only duplicates the work sheet and doesn't place it after the active work sheet but rather at the end of the workbook.