Try testing your Sheets.count statement.

MsgBox ("There are " & Worksheets.Count & " worksheets in this workbook.")

See if that number matches the number of worksheets you see.

wrote in message
ups.com...
On May 18, 1:48 pm, "IanKR" wrote:
Update - more mysterious. When I moved "TR_template" from index 2 to
index 8, the new sheet was inserted at index 2 instead of index 3 as
before, or index 9 as expected. Very peculiar.

Have you been changing the programmatic name (i.e. the one shown in
parentheses in the hirerachy under MicrosoftExcelObjects in the VB
Explorer) of any of the worksheets, by any chance? May be irrelevant, but
I've heard that this is a dangerous thing to do (can produce
unpredictable
results). I'm not sure whether this affects the indexing. When you say
moved
from index 2 to index 8, did you just drag the tab along in
theExcelwindow
to re-order the sheets, or did you do anything in the VBE?

I do change the name of the worksheet that was copied, if that's what
you mean. But that shouldn't affect the copy, which is happening
first. When I moved the sheet, I just dragged the tab.