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:
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.