VBA Duplicate sheet in excel workbook.

[WalkerArkdown]

Hey All,

This has been driving me crazy, and I can't seem to find an answer anywhere.

I'm creating a document at work for creating new item products. The workbooks currently has one sheet visible and the rest hidden. The hidden pages do all the work with lists, and Vlookups ect. The Visible sheet is the main sheet that a user will fill in using validation lists. This sheet is all password protected to stop a user deleting stuff they shouldn't. The entire workbook is protected too.

I have been asked if I can make a function where the user can duplicate the sheet if they want to create more then one item. So far I have a VBA linked to a shape that runs and can duplicate the sheet but I have some issues I can't solve. I have tried two different methods.
------------------------------------------
Method 1: Copy the main sheet, paste at the end and give the user the option to rename the sheet. This works perfect! except....
Problem: Once the sheet duplicates it causes an error where by the user clicks back onto the first/original sheet and they get the generic "You cannot use this command of a protected sheet. To use this command, you must u protect the sheet.". This isn't too bad but is annoying and most users who use this sheet will think something has gone wrong. It will give this error every time you click back onto the first sheet.

Public Sub DuplicateSheet()

    ActiveWorkbook.Unprotect "password"
    
        Dim newName As String
        newName = InputBox("Enter name of new sheet" & vbNewLine & vbNewLine & "ie. Item number, Product Description etc")

        If newName <> "" Then
            ActiveSheet.Copy After:=Worksheets(Sheets.Count)
            On Error Resume Next
            ActiveSheet.Name = newName
        End If
        
     ActiveWorkbook.Protect "password"
     
End Sub

------------------------------------
Method 2: Set a hidden template of the original sheet, so the Main sheet isn't copied. Then copy this sheet to the end of the workbook.
Problem: This just copies the template sheet over the main visible sheet, and causes a massive amount of errors "The name 'Area' already exists. Click Yes to use that version name, or click No to rename of 'Area' you're moving or copying" is jsut the first of about 25 renames. I need this to paste the sheet after the main sheet and not copy it over it.

Public Sub DuplicateSheet()

    ActiveWorkbook.Unprotect "password"
    
Dim Answer As Variant
LastSheet = Sheets.Count
    Sheets("Template").Copy after:=Sheets(LastSheet)
    Answer = InputBox(prompt:="What do you want to name this sheet?", _
        Title:="        We need a name for this sheet")
    ActiveSheet.Name = Answer
        
     ActiveWorkbook.Protect "password"
End Sub

If anyone can recommend anything that would be great.

So Just to summaries, I'm after a sheet duplicate VBA the will copy the main sheet and paste at the end, without causes these errors.

Thank you.

 

[WalkerArkdown]

Tried the nw code and get the compile error.

"Only comments may appear after End Sub, End Function, or End Property

The Line "Sub SheetsVisible(UserChoice As Boolean)" is highlighted yellow
and the word Dim is seleted.

 

[Yongle]

Error description is telling you that there is a minor typo in your module

Check all the areas between each End Sub, End Function etc and the beginning of the next sub
- you should find a rogue character or more somewhere!


Sometimes (due to a typo) VBA does not recognise the beginning of a sub - so look carefully at everything
- can be difficult to spot because we see "what we want to see", VBA sees what is actually there :wink:

Make sure that there is an End Sub at end of original sub etc - that would be my guess

 

[WalkerArkdown]

I went through the code and couldn't find a spelling error or extra space. Retyped the code and it work. Like you say "We see what we want too" so much have been something wrong.

Anyway, all working now. The form does exactly what I want it too, no errors. I'm actually quite proud of it. If it didn't have confidential information on it I'd share it.

Thank you so much for your help Yongle!! Couldn't have done it without you!

 

[Yongle]

You are welcome