Excel copy after problem

[satlow]

I have a workbook with 8 sheets, and am trying to copy one of them to
the end of the workbook, using the code below:

Sheets("TR_template").Copy After:=Sheets(nsheets) 'for some reason
after is ignored

The sheet I am copying, "TR_template" is the second sheet. "nsheets"
is set to the number of sheets in the workbook (8). After the copy is
executed, the new sheet, "TR_template (2)" is inserted immediately
after "TR_template", in position 3. Why isn't it number 9? It
appears that the "after" parameter is ignored.

 

[Norman Jones]

Hi Satlow,

Try something like:

With Sheets("TR_template")
.Copy after:=.Parent.Sheets(.Parent.Sheets.Count)
End With

 

[PCLIVE]

That should work. Just to be sure, are you setting nsheets using:

nsheets = Sheets.Count

 

[IanKR]

I have a workbook with 8 sheets, and am trying to copy one of them to

the end of the workbook, using the code below:

Sheets("TR_template").Copy After:=Sheets(nsheets) 'for some reason
after is ignored

The sheet I am copying, "TR_template" is the second sheet. "nsheets"
is set to the number of sheets in the workbook (8). After the copy is
executed, the new sheet, "TR_template (2)" is inserted immediately
after "TR_template", in position 3. Why isn't it number 9? It
appears that the "after" parameter is ignored.

It should work - this does for me:

Sheets("TR_template").Copy after:=Sheets(ThisWorkbook.Worksheets.Count)

[assuming ThisWorkbook is the wkb in question]

 

[satlow]

All,
Thanks for all your quick responses. I just tried all your
suggestions, and can't make any of them work. The code for nsheets
is:

nsheets = ThisWorkbook.Sheets.Count

I also tried Norman's suggestion of using "parent", and lanKR's
suggestion of using "ThisWorkbook.Sheets.Count" explicitly, and in all
cases the sheet is still inserted after "TR_template" instead of at
the end. Any other ideas?

 

[IanKR]

All,

Thanks for all your quick responses. I just tried all your
suggestions, and can't make any of them work. The code for nsheets
is:

nsheets = ThisWorkbook.Sheets.Count

I also tried Norman's suggestion of using "parent", and lanKR's
suggestion of using "ThisWorkbook.Sheets.Count" explicitly, and in all
cases the sheet is still inserted after "TR_template" instead of at
the end. Any other ideas?

Just a thought - is ThisWorkbook the workbook where the code is - and where
Sheets("TR_template") is? May sound a silly Q, but if the code is in a
different workbook it's referencing the number of sheets in a different
workbook.

 

[satlow]

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.

 

[satlow]

Just a thought - is ThisWorkbook the workbook where the code is - and where
Sheets("TR_template") is? May sound a silly Q, but if the code is in a
different workbook it's referencing the number of sheets in a different
workbook.

Good thought, but ThisWorkbook is the only one open, where the code
is, and where TR_template is. BTW, "add" works properly, but "copy"
does not.

 

[IanKR]

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 Microsoft Excel Objects 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 the Excel window
to re-order the sheets, or did you do anything in the VBE?

 

[IanKR]

Just a thought - is ThisWorkbook the workbook where the code is -

Good thought, but ThisWorkbook is the only one open, where the code
is, and where TR_template is. BTW, "add" works properly, but "copy"
does not.

OK - that excludes that, then!

 

[satlow]

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.

 

[IanKR]

Have you been changing the programmatic name (i.e. the one shown in

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.

No, I meant changed the *programmatic* name of any of the worksheets (which
you can only do in the VBE or via VBA) prior to the copy. I don't mean
change the "visible" name on the worksheet tab... I'm clutching at straws
here. Tried copying the sheet(s) and code to a blank fresh workbook and
trying it there?

 

[PCLIVE]

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.

 

[satlow]

Mystery solved!
All the sheets except the first were hidden. Copy after put the new
sheet after the last VISIBLE sheet. When I made the last sheet
visible, the new sheet was inserted after the last sheet.

Interestingly enough, ADD puts the new sheet at the end, even when all
the sheets except the first are hidden.