VBA - Bug? Copying worksheet after last sheet doesn't work

Chris The Rock

Active Member
Joined
Feb 24, 2002
Messages
287
In my workbook, I have a couple of hidden sheets.

During my code, I am copying one of those sheets, and by the following code, attempting to place it as the last sheet in the workbook, and then make the new sheet visible. I'm using the sheet's CodeName property to identify it.

Code:
MYSHEET.Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.count).Visible = True

The problem is - the new copy of the sheet is not being added as the last sheet. It is being added immediately after the sheet that's being copied. This causes a chain reaction of problems in the rest of my code, where the newest sheet is expected to be the last one in the workbook.

In a new workbook, I cannot reproduce the problem. Also, if I unhide my hidden sheets, it works fine.

Any ideas? Is this a known bug?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
It is not a bug. Evidently you have deleted sheets from your workbook and when new sheets are added, they tend to "fill-in" the slots left by the deleted ones. That is why you cannot reproduce the "problem" in a new workbook.
Since it works fine when all sheets are visible, I would suggest you unhide all worksheets at the beginning of your code, the re-hide them at the end.
 
Upvote 0
I realize that the normal operation of the program is to add sheets where old sheets used to be, but the "after" keyword is supposed to direct the code to direct the code to put the new copy after the sheet's index number that you specify, regardless of the number of sheet's you have deleted.

I may have to do what you've suggested, but I think there is a problem. I'm still working to uncover it. If I discover an answer, I will update this post.
 
Upvote 0
Do you have any chart sheets, or other types of sheet other than worksheets?
 
Upvote 0
Re-arranging the order of the sheets (placing the two hidden sheets first in the workbook - Index #'s 1 & 2) seems to have alleviated the problem. Now, when one of the hidden sheets is copied, it is properly added to the workbook after the last sheet, and becomes the new last sheet.

I've seen some strange things before, and this is certainly one of them. I wonder what caused this, and how I can avoid it?
 
Upvote 0
Do you have any chart sheets, or other types of sheet other than worksheets?

No, not presently, but I do intend on using Chart sheets, which is why I've been careful to refer to the count of Sheets, not worksheets.

I've already learned from this mistake before! :lol:
 
Upvote 0

Forum statistics

Threads
1,223,150
Messages
6,170,378
Members
452,322
Latest member
CrimsonCoure

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