VBA Code to duplicate worksheet with user added inputs is entering input data into the wrong sheet

[Scd1216]

[FONT=&quot]I have a macro that duplicates a template worksheet. When I run the macro I have a series of input boxes. The macro is supposed to be placing the information entered into those input boxes into the specified cells on the newly duplicated template sheet which is named after whatever the user puts into the tab name input box.

[/FONT]
[FONT=&quot]Instead what's happening is that the template sheet gets copied as "template (2)" and the information from the input boxes is being entered into an unrelated sheet in the workbook. The unrelated sheet is hidden and is the last tab in the book. That hidden sheet is also being renamed as whatever is being entered into the "tab name" input box.

[/FONT]
[FONT=&quot]Ideally, I just want the new template sheet to be placed between two hidden placeholder sheets named "Start" and "End". Can anyone help with this, please? The macro was working fine before when it's primary function was to copy and paste a specified range from the template sheet onto a new sheet, but once I changed the macro so that it would duplicate the template sheet, these issues occurred. Below is the relevant code:

[FONT=&quot]On Error Resume Next[/FONT][/FONT]
[FONT=&quot] tabName = InputBox("Enter a name for this tab, do not use spaces and do not use full name [Abbreviate]") newName = InputBox("Object Name?") StrType = InputBox("What Type of Object is This?") StrCom = InputBox("Is this Object a Content Comparable or a Local Comparable?") strCity = InputBox("Object City? If unknown, enter ''0'' and delete within sheet") StrState = InputBox("Object State?") lngAtt = InputBox("Object Attendance? If unknown, enter ''0'' and delete within sheet") IngAP = InputBox("Object Price? If free enter ''0''") IngMSA = InputBox("MSA of Object Location? If unknown, enter ''0'' and delete within sheet") IngONV = InputBox("Annual Overnight Visitors?") IngExSqft = InputBox("Object Total Size? If unknown, enter ''0'' and delete within sheet") IngTaxYear = InputBox("Year?") On Error GoTo 0 If newName = "" Or newName = "False" Then Exit Sub Application.ScreenUpdating = False Sheets("Template").Copy After:=Sheets(ThisWorkbook.Worksheets.Count) Set wsNew = Sheets(ThisWorkbook.Worksheets.Count) With wsNew .Name = tabName 'Fill in the info somewhere on this sheet If strCity <> "" Then .Range("A2").Value = strCity & ", " & StrState End If .Range("A1").Value = newName .Range("AB2").Value = lngAtt .Range("AC2").Value = IngMSA .Range("AD2").Value = IngExSqft .Range("H1").Value = IngTaxYear  [/FONT][FONT=&quot][FONT=&quot] End With[/FONT]

Any help you can provide would be greatly appreciated and if there's any extra info that I can provide to help clear up anything that isn't clear please let me know. [/FONT]

 

[Scd1216]

Sorry the code formatting got screwed up and I cant edit my post. Hopefully this is easier to read.

[FONT=&quot]On Error Resume Next
    tabName = InputBox("Enter a name for this tab, do not use spaces and do not use full name [Abbreviate]")
   newName = InputBox("Object Name?")
   StrType = InputBox("What Type of Object is This?")
   StrCom = InputBox("Is this Object a Content Comparable or a Local Comparable?")
   strCity = InputBox("Object City? If unknown, enter ''0'' and delete within sheet")
   StrState = InputBox("Object State?")
   lngAtt = InputBox("Object Attendance? If unknown, enter ''0'' and delete within sheet")
   IngAP = InputBox("Object Price? If free enter ''0''")
   IngMSA = InputBox("MSA of Object Location? If unknown, enter ''0'' and delete within sheet")
   IngONV = InputBox("Annual Overnight Visitors?")
   IngExSqft = InputBox("Object Total Size? If unknown, enter ''0'' and delete within sheet")
   IngTaxYear = InputBox("Year?")
[/FONT][FONT='inherit']

[/FONT][FONT=&quot]On Error GoTo 0 If newName = "" Or newName = "False" Then Exit Sub    Application.ScreenUpdating = False   Sheets("Template").Copy After:=Sheets(ThisWorkbook.Worksheets.Count)
   Set wsNew = Sheets(ThisWorkbook.Worksheets.Count)   With wsNew    .Name = tabName
    'Fill in the info somewhere on this sheet
    If strCity <> "" Then
        .Range("A2").Value = strCity & ", " & StrState    End If
    .Range("A1").Value = newName
    .Range("AB2").Value = lngAtt
    .Range("AC2").Value = IngMSA
    .Range("AD2").Value = IngExSqft
    .Range("H1").Value = IngTaxYear  End With[/FONT][FONT='inherit']


[/FONT]

 

[Fluff]

Try

   Set wsNew = ActiveSheet

 

[Scd1216]

So everything would be the same except in this section with the underlined and italicized being the edited code

On Error GoTo 0 

If newName = "" Or newName = "False" Then Exit Sub    

Application.ScreenUpdating = False   

Sheets("Template").Copy After:=Sheets(ThisWorkbook.Worksheets.Count)

[I][U]Set wsNew = ActiveSheet[/U][/I](ThisWorkbook.Worksheets.Count)

 

[Fluff]

Yup, that's right, but remove the part in brackets. Assuming that ThisWorkbook is the active workbook

 

[Scd1216]

So I updated the code to this:

 If newName = "" Or newName = "False" Then Exit Sub


    Application.ScreenUpdating = False


    Sheets("Template").Copy Before:=wBook.Worksheets("Summary")


    Set wsNew = ActiveSheet

And it's working correctly for the most part. I decided to delete the hidden "end" sheet and instead just use the "summary" sheet as the placeholder since it was basically in the same spot and "summary" was visible. The only issue I'm having is that if I hide the template sheet, when the macro is run the code renames the "Summary" tab to whatever I inputted as the new tab name and enters all the info from the input boxes into that "Summary" tab. It also copies the template sheet as "Template (2)" and hides it as well. If I unhide the template sheet it works exactly as it's supposed. Is there any way I can change the code so that I can keep the template sheet hidden but still have the macro function in the way it does when it's not hidden?

So to summarize:

When template sheet is visible=works.

When template sheet is hidden=Renames wrong sheet, inputs data in the wrong sheet, and copies the template sheet as "template (2)" and hides it.

 

[Fluff]

If you've removed the hidden sheet then go back to what you had before & add the line in blue to make the new sheet visible

   Set wsNew = Sheets(ThisWorkbook.Worksheets.Count)
   With wsNew
   [COLOR=#0000ff].Visible = True[/COLOR]
   .name = "me"