Copy Template multiple times and rename using list

[Macroless99]

Hi All

I need some help with VBA code to copy a template tab multiple times and rename each new tab from a list.
* My template tab is called StudentA
* My list is in a tab Data Input cells A4:A50

What I currently have is:

[/COLOR]

Sub Copy_Sheets()
    Dim i As Integer
    Dim wks As Worksheet
     
    Set wks = Sheets("DataInput")
     
    For i = 4 To 50
        Sheets("StudentA").Copy After:=Sheets(6)
        ActiveSheet.Name = wks.Cells(i, 1)
        ActiveSheet.Cells(1, 2) = wks.Cells(i, 1)
    Next
End Sub[COLOR=#333333]

When I run it, it copies the template tab once but doesn't rename and returns a message "400".

Would really appreciate any help you can give me!!!

Kirsty

 

[Momentman]

What line is highlighted when you get the error 400?


Is it "Data Input" or "DataInput"?

 

[Macroless99]

Data tab name is DataInput.

K

 

[Rick Rothstein]

Data tab name is DataInput.

What is the answer to Momentman's first question?

 

[Macroless99]

Oops missed the first part. It's not automatically going into debug mode and nothing is being highlighted. I have been assuming it is one of these lines because it is performing the copy of the "StudentA" tab but not renaming the tab before giving me the error:

ActiveSheet.Name = wks.Cells(i, 1)        
ActiveSheet.Cells(1, 2) = wks.Cells(i, 1)

 

[Momentman]

Oops missed the first part. It's not automatically going into debug mode and nothing is being highlighted. I have been assuming it is one of these lines because it is performing the copy of the "StudentA" tab but not renaming the tab before giving me the error:

ActiveSheet.Name = wks.Cells(i, 1)        
ActiveSheet.Cells(1, 2) = wks.Cells(i, 1)

I have run the code as is and it works.

Like you said, the sheet is created but probably not renamed, so I would suggest you check the "range/cell" the sheet name is being sourced from

Check what the value of "I" is when the code stops and identify the content of wks.cells(I,1)

Confirm that the cell(e.g A4) is not blank or contains some "illegal" characters