VBA Copy Sheet in Excel — Duplicate a Worksheet Without Spawning a New Workbook

TL;DR — Worksheet.Copy takes the same Before:=/After:= positioning you use for Add, but with two differences that catch everyone. First, it returns nothing — there is no Set ws = sheet.Copy, so you grab the fresh copy through ActiveSheet on the very next line. Second, and this is the big one: ws.Copy with no destination does not copy inside the workbook — it creates a brand-new workbook containing the copy. Always pass a Before or After unless a new file is exactly what you want.

Sub DuplicateTemplate()
    Dim src As Worksheet, copy As Worksheet
    Set src = ThisWorkbook.Worksheets("Template")

    src.Copy After:=ThisWorkbook.Worksheets(Worksheets.Count)  ' duplicate to the end
    Set copy = ActiveSheet                                     ' the copy is now active
    copy.Name = "Template " & Format(Now, "hhmmss")            ' rename it immediately
End Sub

Worksheet.Copy is the code behind right-click a tab ▸ Move or Copy ▸ Create a copy. It is how report macros stamp out a fresh sheet from a template each month. It is also the single most surprising method in the sheet-management family, because the "obvious" call — Sheets("Template").Copy with no arguments — does something entirely different from what the name suggests.

What you'll learn

The mental model — Copy is Add's twin: same positioning, no return value
The surprise that breaks most macros — a bare Copy spawns a new workbook
How to reference the copy when the method returns nothing
What travels with a copied sheet — formulas, formatting, code, and defined names
How Move mirrors Copy with the opposite intent, and how to copy to another workbook

The mental model: Copy is Add's twin, with no return value

Adding and copying a sheet share a grammar. Both accept Before:= and After:= to say where the sheet lands, and both default to a position rather than the end. The difference is that Add is a function that hands you the new sheet, while Copy is a method that returns nothing at all. You cannot write Set ws = src.Copy; it is a compile error.

So Excel leaves you exactly one way to grab the fresh duplicate: right after Copy runs, the copy becomes the active sheet. Capture it on the next line, before anything else can steal focus:

src.Copy After:=Worksheets(Worksheets.Count)
Dim copy As Worksheet
Set copy = ActiveSheet          ' this IS the copy - grab it now

This is one of the rare places where relying on ActiveSheet is correct rather than sloppy, because Copy genuinely leaves the new sheet active and there is no better handle. The discipline is to do it immediately — a Calculate, a DoEvents, or an event handler between Copy and Set copy = ActiveSheet can move focus and hand you the wrong sheet.

The surprise that breaks most macros: a bare Copy spawns a new workbook

Here is the behavior that sends people to search engines. You would reasonably expect Sheets("Template").Copy — with no arguments — to duplicate the sheet inside the current workbook. It does not. A Copy with neither Before nor After creates a new, single-sheet workbook and puts the copy there. Run it in a loop and you get a pile of new windows instead of a pile of new tabs.

src.Copy                        ' NOT a local duplicate - a whole new workbook appears

The rule is simple: always pass a destination when you want an in-workbook duplicate. The no-argument form is not a bug, though — it is the cleanest idiom in all of VBA for exporting one sheet as its own file:

src.Copy                                 ' new workbook with just this sheet
ActiveWorkbook.SaveAs "C:\Reports\September.xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close SaveChanges:=False

So the same method serves two intents depending on one argument. Decide which you mean, and never let a missing After turn a "duplicate this tab" into "open forty workbooks."

What travels with a copied sheet

Copy clones the object, not just the values — and that is more than most people picture. A copied sheet brings its formulas, formatting, charts, shapes, conditional formatting, the code in its sheet module, and any sheet-scoped defined names. Two consequences follow:

Formulas that referenced other sheets keep those references, so a copy inside the same workbook can quietly point back at the original's neighbors — check whether you wanted the links retargeted.
Duplicated defined names are the classic side effect: copy a sheet with a scoped name and Excel may prompt about a name that already exists, or silently create a second, differently scoped one.

If you only want the data, do not copy the sheet — copy the range. dst.Range("A1:Z100").Value = src.Range("A1:Z100").Value moves values with none of the baggage. Copying the whole sheet is the right tool when you deliberately want the template — layout, formulas, and code — reproduced intact.

Naming the copy, and how Move mirrors Copy

Excel auto-names a duplicate Template (2), Template (3), and so on, so you almost always rename it at once. Since there is no return value, rename through the active sheet on the line right after Copy, and the same rules from the Add article apply — 31 characters, unique, no : \ / ? * [ ].

Worksheet.Move is Copy's mirror image. It takes the identical Before:=/After:= arguments, but it relocates the sheet instead of duplicating it, and a bare Move with no destination also opens a new workbook — moving the sheet out of the current one entirely. Same argument shape, opposite intent:

src.Move After:=Worksheets(Worksheets.Count)   ' relocate to the end (no duplicate)
src.Copy To:=Workbooks("Report.xlsx").Worksheets(1)   ' copy INTO another open workbook

To copy into another workbook, target its sheet in the Before/After argument — both files must be open. The judgment across the whole method: always pass a destination, capture the copy through ActiveSheet on the next line, remember the copy carries code and names, and reach for the bare form only when "new workbook" is the actual goal.

How ExcelMaster helps

Copying a sheet hides a landmine — the no-argument form opens a new workbook instead of duplicating — and two quieter traps: no return value to grab, and a copy that drags its code and defined names along with it.

ExcelMaster lets you say the intent plainly — "duplicate the Template sheet to the end and name it for today" or "export this sheet as its own file" — and it writes the right form for each: a positioned Copy with an ActiveSheet capture and a rename for the in-workbook duplicate, or a bare Copy plus SaveAs for the export. It also warns when a copied sheet carries formulas or names that will collide. You keep the workbook and the code.

Frequently asked questions

How do I copy a worksheet in VBA?

Use Worksheet.Copy with a destination: src.Copy After:=Worksheets(Worksheets.Count) duplicates the sheet to the end. Because Copy returns nothing, grab the new sheet through ActiveSheet on the next line — Set copy = ActiveSheet — then rename it. Always pass Before:= or After:=; the no-argument form does something different.

Why does my Copy macro open a new workbook?

Because you called Copy with no Before or After argument. A bare Worksheet.Copy does not duplicate inside the current workbook — it creates a new, single-sheet workbook containing the copy. Pass a destination (After:=Worksheets(Worksheets.Count)) to duplicate in place, and reserve the argument-less form for exporting a sheet as its own file.

How do I get a reference to the copied sheet?

Copy has no return value, so you cannot write Set ws = src.Copy. Instead, the copy becomes the active sheet immediately after the call, so capture it with Set copy = ActiveSheet on the very next line — before any Calculate, DoEvents, or event handler can move focus and hand you the wrong sheet.

How do I copy a sheet to another workbook?

Target a sheet in the other workbook inside the positioning argument, with both files open: src.Copy After:=Workbooks("Report.xlsx").Worksheets(Worksheets.Count). The copy carries its formulas, formatting, and code; watch for formulas that referenced the source workbook, as they may point back at the original file.

What is the difference between Copy and Move?

Worksheet.Copy duplicates the sheet; Worksheet.Move relocates it, removing it from its original position. Both take the same Before:=/After:= arguments, and both open a new workbook when called with no destination. Use Copy to stamp out a duplicate and Move to reorder or extract a sheet.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-14.