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

[Chris The Rock]

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.

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?

 

[Datsmart]

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.

 

[Chris The Rock]

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.

 

[Norie]

Do you have any chart sheets, or other types of sheet other than worksheets?

 

[Chris The Rock]

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?

 

[Chris The Rock]

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!