Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. 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? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel copy after problem
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel copy/paste problem | Excel Discussion (Misc queries) | |||
copy problem using excel VBA | Excel Programming | |||
Excel Copy/Paste Problem | Excel Discussion (Misc queries) | |||
Problem: How to copy excel sheet in C++ | Excel Programming | |||
Excel VBA - Copy Folder problem | Excel Programming |