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.

 

[Yongle]

Can you post the line of code which is triggering the message under method1
It seems that duplicating the visible sheet is the easier option.
What needs removing from the new sheet after duplication?
Other than renaming the sheet, does the user need to do anything else to the new sheet ?
Should new sheet be protected ?
Should the old sheet now be hidden ?

Have I missed anything?

 

[WalkerArkdown]

Can you post the line of code which is triggering the message under method1

The code for method 1 doesn't error. It successfully copies the sheet then puts it at the end as an exact duplicate of the original. However I have now changed it to the following, and added a new macro button that allows the user to click to change the name after.

Sub DuplicateSheet()

    ActiveWorkbook.Unprotect "password"
    
        Dim WS As Worksheet, WB As Workbook
        Set WB = ActiveWorkbook
        Set WS = WB.Sheets("New item set up")
        WS.Copy after:=Sheets(WB.Sheets.Count)
        
     ActiveWorkbook.Protect "password"
     
End Sub

It seems that duplicating the visible sheet is the easier option.

Yes duplicating a visible sheet is the easiest I've found.

What needs removing from the new sheet after duplication?

Nothing, I want an exact copy as a second sheet. The idea is the user can fill in a form for "Item number 1", then they duplicated the form to make "Item number 2" and so on. Previously its been done with individual excel files. But due to having to protected the sheet, the user shouldn't know the password to unlock it and copy it.

Other than renaming the sheet, does the user need to do anything else to the new sheet ?

No, they will have a new blank sheet to fill in.

Should new sheet be protected ?

Yes, I want it to be protected, which works does happen.

Should the old sheet now be hidden ?

No, this will be the first sheet the user would fill in.

The only issue I'm getting, is the user will Duplicate the sheet, and then after they can rename that new sheet. When the user clicks back onto the first sheet (that's been duplicated from), the Protection error message appears. Which is what I can't stop and don't understand what is causing it.

 

[Yongle]

The only issue I'm getting, is the user will Duplicate the sheet, and then after they can rename that new sheet. When the user clicks back onto the first sheet (that's been duplicated from), the Protection error message appears. Which is what I can't stop and don't understand what is causing it.

It sounds like the original sheet cannot be activated once the user has landed on another worksheet
- the duplicated worksheet automatically becomes the active sheet

Fixing that is easy, but let's just make sure that there isn't any other code that will trip us up

Does the user need an ongoing facility to hop in either direction between the 2 sheets after the sheet has been duplicated ?

Is there any code in the sheet module of the original sheet?
- Worksheet_Activate ?
- Worksheet_Deactivate ?

Failing that - is there anything in ThisWorbook module
- Workbook_SheetActivate ?
- Workbook_SheetDeactivate ?

 

[WalkerArkdown]

The user can create as many duplicate sheets as they need to. Some cases mean they have to create 20 new items. Currently all with individual excel files.
So they would need to be able to got to any sheets they need.

The only code in the original sheet is a "Private Sub Worksheet_Change" This is a bit of code that allows the user to un-hide some rows if they select Yes in the relevant drop down box, then re-hire when they select No.

As for the "ThisWorkbook". I have a little bit of code that shows the user login name. This was planned to allow certain users to have access to hidden options in the sheet, but was just a bit beyond my excel abilities to get to work, this is just a simple.

Private Sub Workbook_Open()

    Range("A1").Value = Environ("username")
    
End Sub

Anything else is Macros with ActiveSheet or ActiveWorkbook to make sure the macro runs on the current sheet that is selected.

 

[Yongle]

Under worksheet protection in hte ORIGINAL sheet are there any cells that the user is not allowed to select ?

 

[Yongle]

Also what hapeens with this amendment to your procedure
Can the user now hop between sheets or not?

Sub DuplicateSheet()

    ActiveWorkbook.Unprotect "password"
  
        Dim WS As Worksheet, WB As Workbook
        Set WB = ActiveWorkbook
        Set WS = WB.Sheets("New item set up")
        WS.Copy after:=Sheets(WB.Sheets.Count)
        [COLOR=#ff0000]WS.Activate[/COLOR]
     ActiveWorkbook.Protect "password"
     
End Sub

If that works, test this

Sub DuplicateSheet()

    ActiveWorkbook.Unprotect "password"
    
        Dim WS As Worksheet, WB As Workbook
        Set WB = ActiveWorkbook
        Set WS = WB.Sheets("New item set up")
        WS.Copy after:=Sheets(WB.Sheets.Count)
      
     ActiveWorkbook.Protect "password"
     
   [COLOR=#ff0000] WS.Activate[/COLOR]
End Sub

 

[WalkerArkdown]

Good Morning,

I seem to have found a way round this issue. It's probably the longest way round ever! but it works.

Sub DuplicateSheet()

    ActiveWorkbook.Unprotect "password"
    
        Dim WS As Worksheet, WB As Workbook
        Set WB = ActiveWorkbook
        Set WS = WB.Sheets("TEMPLATE")
       'Set WS = WB.ActiveSheet
            Worksheets("TEMPLATE").Visible = True
            Worksheets("sheet2").Visible = True
            Worksheets("sheet3").Visible = True
            Worksheets("sheet4").Visible = True
            Worksheets("sheet5").Visible = True
            Worksheets("sheet6").Visible = True
            Worksheets("sheet7").Visible = True
            Worksheets("sheet8").Visible = True
        WS.Copy After:=Sheets(WB.Sheets.Count)
            Worksheets("TEMPLATE").Visible = False
            Worksheets("sheet2").Visible = False
            Worksheets("sheet3").Visible = False
            Worksheets("sheet4").Visible = False
            Worksheets("sheet5").Visible = False
            Worksheets("sheet6").Visible = False
            Worksheets("sheet7").Visible = False
            Worksheets("sheet8").Visible = False
        
    ActiveWorkbook.Protect "password"
    
    Call RenameSheet
    Call Reset_Form_Auto

    
End Sub

As you can tell from the code, the macro unlocks the Workbook. Copies the "TEMPLATE" worksheet, then unhides every sheet. Pastes the Copied sheet at the end, then rehindes the sheets.

The two call Macros are for an Input box that allows the user to rename the sheet as its created, and the second resets the form. Which I don't actually need now as the TEMPLATE is hidden and will already be blanks.

But this solves my password protected message.

Thank you for your help with this issue though. I will save a copy and try you method though.

 

[Yongle]

or you could add call sub to hide and unhide and avoid duplicating lines of code for True\False

Sub DuplicateSheet()
    ActiveWorkbook.Unprotect "password"
    Dim WS As Worksheet, WB As Workbook
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("TEMPLATE")
  
    [COLOR=#008080]SheetsVisible True[/COLOR]
    WS.Copy After:=Sheets(WB.Sheets.Count)
    [COLOR=#008080]SheetsVisible False[/COLOR]
    ActiveWorkbook.Protect "password"
    
    Call RenameSheet
    Call Reset_Form_Auto
End Sub

Sub being called:

Sub SheetsVisible(UserChoice As Boolean)
    Dim sh
    For Each sh In Array("TEMPLATE", "sheet2", "sheet3", "sheet4", "sheet5", "sheet6", "sheet7", "sheet8")
        Sheets(sh).Visible = UserChoice
    Next
End Sub

 

[WalkerArkdown]

Hmmm, that would be much better then all those lines! Will edit the code later.

Thank you