Results 1 to 2 of 2

Thread: Copy Tabs with VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Tabs with VBA

    Hi.

    I am trying to write a simple code which does the following, with the click of a button on its respective tab.

    1. Unprotect all sheets with a password
    2. Copies the selected / active sheet to a new sheet, placed at the end of existing sheets
    3. Displays a message box to name the new sheet
    4. Protect all sheets with a password

    Reason for nr 1 and 4 is error messages when copying and moving between the sheets while protected - this trick removed the error. And the sheets needs to be protected for "idiot-proofing" reasons.

    But, the wrong sheet gets copied. Assume I have 5 sheets: A, B, C, D and E.
    If my active sheet is "E", and I press my macro-connected button it copies "D".
    If my active sheet is "A", and I press my macro-connected button it still copies "D".

    I suspect steps 1 and/or 4 is messing with something...

    Fairly new to coding, so cut and paste from various online sites + trying and failing is my method of working. But great fun!

    Any tips or tricks?

    Code:

    Sub

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    ws.Unprotect "password123"
    Next ws


    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = InputBox("Name of New Invoice Tab:")

    For Each ws In ThisWorkbook.Worksheets
    ws.Protect "password123"
    Next ws

    End Sub

  2. #2
    New Member
    Join Date
    Nov 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Tabs with VBA

    Hi.

    I am trying to write a simple code which does the following, with the click of a button on its respective tab.

    1. Unprotect all sheets with a password
    2. Copies the selected / active sheet to a new sheet, placed at the end of existing sheets
    3. Displays a message box to name the new sheet
    4. Protect all sheets with a password

    Reason for nr 1 and 4 is error messages when copying and moving between the sheets while protected - this trick removed the error. And the sheets needs to be protected for "idiot-proofing" reasons.

    But, the wrong sheet gets copied. Assume I have 5 sheets: A, B, C, D and E.
    If my active sheet is "E", and I press my macro-connected button it copies "D".
    If my active sheet is "A", and I press my macro-connected button it still copies "D".

    I suspect steps 1 and/or 4 is messing with something...

    Fairly new to coding, so cut and paste from various online sites + trying and failing is my method of working. But great fun!

    Any tips or tricks?

    Code:

    This seemed to fix it

    Quote Originally Posted by roboed View Post
    Sub

    Dim ws As Worksheet


    ActiveSheet.Unprotect "password123"

    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = InputBox("Name of New Invoice Tab:")

    For Each ws In ThisWorkbook.Worksheets
    ws.Protect "password123"
    Next ws

    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •