Worksheet Not Copying

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Try it this way

Code:
Sheets(WSName).Copy After:=Sheets(Sheets.Count)
Set ws_str = Sheets(Sheets.Count)
ws_str.Name = tab_sal
 
Upvote 0
That was most helpful. Thank you for your help and patience.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top