Likes Likes:  0
Results 1 to 9 of 9

Thread: Worksheet Not Copying

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    3,129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Worksheet Not Copying

    I am having a problem (more likely just a brain fart of sorts) with this code ...

    Code:
    Set ws_atr = Worksheets(WSName).Copy
    ws_atr.Name = tab_sal
    I am trying to coipy worksheet WSName (variable) to the workbook set it worksheet ws_atr and rename it to the variable tab_sal.

    The sheet isn't copying.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,837
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Worksheet Not Copying

    There are a couple of thing that prevent the code from working. But try this
    Code:
    Worksheets(WSName).Copy
    Set ws_atr = ActiveWorkbook.Sheets(1)
    ws_atr.Name = tab_sal
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,090
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Worksheet Not Copying

    If you are going to copy the sheet within the same book that contains the macro:


    Code:
        Sheets(wsName).Copy after:=Sheets(Sheets.Count)
        Set ws_atr = ActiveSheet
        ws_atr.Name = tab_sal
    Last edited by DanteAmor; Apr 20th, 2019 at 05:19 PM.
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    3,129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Not Copying

    Thank you both ...
    The sheet is copying now, but because it was copied in a hidden condition, (the source sheet was hidden and the copied version is hidden) the name isn't being applied to the newly copied sheet. Rather, the visible sheet is being renamed. I suspect that the activesheet is not the right one.

    Code:
                        With wb_pstaff
                            Sheets(WSName).Copy after:=Sheets(Sheets.Count)
                            Set ws_atr = ActiveSheet
                            ws_atr.Name = tab_sal
                        End With

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,090
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Worksheet Not Copying

    Try this:

    Code:
        Application.ScreenUpdating = False
        
        Sheets(wsName).Visible = -1
        Sheets(wsName).Copy After:=Sheets(Sheets.Count)
        Set ws_atr = ActiveSheet
        ws_atr.Name = tab_sal
        ws_atr.Visible = 0
        Sheets(wsName).Visible = 0
    Regards Dante Amor

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,837
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Worksheet Not Copying

    Try it this way

    Code:
    Sheets(WSName).Copy After:=Sheets(Sheets.Count)
    Set ws_str = Sheets(Sheets.Count)
    ws_str.Name = tab_sal
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  7. #7
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    3,129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Not Copying

    That was most helpful. Thank you for your help and patience.

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,837
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Worksheet Not Copying

    Quote Originally Posted by Ark68 View Post
    That was most helpful. Thank you for your help and patience.
    You're welcome,
    Regards, JLG
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,090
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Worksheet Not Copying

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

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
  •