Solved: Worksheet Copy creates new Workbook

[jwise]

I have a macro which copies a template worksheet, renames it, and then populates this new worksheet with data from another worksheet. The code is working, but it is creating this new worksheet in another workbook when I want this additional worksheet in the same workbook as all other activity in this macro. Here is the loop where the copy is done:

[vba] For Each wkSheet In ThisWorkbook.Sheets

If Left(wkSheet.Name, 2) = "D_" Then
ThisWorkbook.Worksheets(template.Name).Copy
Set newSheet = ActiveSheet
newSheet.Name = Right(wkSheet.Name, Len(wkSheet.Name) - 2)
Call InsData(newSheet, wkSheet)
End If

Next wkSheet [/vba]

I searched and could not find anything close... Any ideas about what I'm doing wrong? This is Office 2003.

[mikerickson]

When using Sheet.Copy, if both the After and Before arguments are omitted, Excel creates a new Workbook and copies the sheet to it.

This will copy template.Name without creating a new workbook.
[VBA]With ThisWorkbook
.Worksheets(template.Name).Copy After:=.Sheets(.Sheets.Count)
End With[/VBA]

[jwise]

Thanks for the solution to my problem.

Did I miss something in the documentation? I'm spending a lot of time with issues like this, so I'm trying to determine exactly where I'm errring. If experience taught you this, then I won't feel so bad. If the doc says this, then I didn't do a goood job of research.

Thanks again for the solution.

[rory]

From the VB Help in Excel (note the Remarks section):

Copies the sheet to another location in the workbook.
expression.Copy(Before, After)

expression Required. An expression that returns one of the above objects.
Before Optional Variant. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.
After Optional Variant. The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.
Remarks

If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.

[jwise]

Unfortunately, I can not find the information you showed in the help file.

I asked about this because this seems to be a recurring problem for me. My objective was to prevent the problem, not just cure it! (1) I had no doubt your suggestion would work, and (2) when I saw the copied text from the "help" files I knew I missed something.

I searched the help files for "Copy" and "Worksheet". What was your search argument. I found nothing close.

Most of the time I do searches I get lots of irrelevant stuff, and I eventually find what I am looking for. It seems the search engine should find all "abcd" as a word before finding "abcd...", and this feature annoys me.

If I'm using the "search box", it has "Microsoft Office Online" in a drop-down above the search line. Is this where you key your search word?

Thanks again.

[jwise]

Naturally, after my reply, I found the remarks section in the help file, so I think this issue is closed.

The greater issue is that I overlooked this because I saw it before. Curing this problem is more difficult than just improving my searches!

Thanks again for the information and your patience.