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.

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

nsheets = Sheets.Count


wrote in message
oups.com...
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.

Hi Satlow,

Try something like:

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


---
Regards,
Norman


wrote in message
oups.com...
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.

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?

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.

On May 18, 1:20 pm, "IanKR" wrote:
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.

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.

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.

OK - that excludes that, then!

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.

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?

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.

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]