Duplicate Active worksheet and name the worksheet based on cell value

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

    I've placed the following code in a module:

    Code
    1. Function Sheet_Exists(WorkSheet_Name As String) As Boolean
    2. Dim Work_sheet As Worksheet
    3. Sheet_Exists = False
    4. For Each Work_sheet In ThisWorkbook.Worksheets
    5. If Work_sheet.Name = WorkSheet_Name Then
    6. Sheet_Exists = True
    7. End If
    8. Next
    9. 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.

  • I should clarify, that i'm testing this code with in the VBA editor. The "CommandButton1_Click()" on the DATA worksheet isn't going to work if it's on the DATA tab because that's not the worksheet I want to copy. Will need to put a button on the active work sheet that calls the VBA

  • Try this. Place the code in a Standard Module and callCopySheets from a Forms Button or Shape


  • Sorry, missed an End If


  • Still getting an error on "Invalid Procedure Call..." for the first "For".

  • Try this