Copy Current Worksheet into a New Workbook Using Macros In Excel

Have a worksheet that you want to copy into a new worksheet? You can accomplish that with a single line of Excel Macro code! It will copy current worksheet into a new workbook. So in this article, I’m going to walk you through exactly how I do it—step by step. Whether you’re new to macros or looking for a simple script that just works, I’ll break it down so you can do it confidently (and never again waste time copy-pasting manually).

Key Takeaways:

• You can automate copying a worksheet with just one line of VBA code.
• No need to manually right-click, move, and save sheets anymore.
• The Developer tab must be enabled to use VBA macros.
• This macro only copies the active sheet, not the entire workbook.
• It instantly creates a new workbook and pastes the copied sheet into it.

 

 

Why I Needed This Macro in the First Place

Let me start by sharing the pain point. I manage multiple project reports, and very often, I have to extract just one sheet from a massive workbook—send it to a client, archive it for compliance, or simply analyze it separately. Doing this manually meant:

STEP 1: Right-click the worksheet tab you want to copy. Select Move or Copy from the context menu.

STEP 2: In the ‘To book:’ dropdown, select (new book). Check the box that says Create a copy. Click OK.

The sheet opens in a new workbook — now press Ctrl + S to save it.

That’s when it hit me: Why not automate this with a macro?

Copy Current Worksheet into a New Workbook Using Macros

Enable Developer Tab

Here are the quick steps to enable the Developer tab in Excel.

STEP 1: Open Excel and go to the File tab (top-left corner).

STEP 2: Click Options at the bottom of the left menu.

STEP 3: In the Excel Options dialog box, click Customize Ribbon on the left.

STEP 4: On the right, under Main Tabs, check the box labeled Developer. Click OK.

You’ll now see the Developer tab appear on the Excel ribbon, usually between View and Help. From here, you can access Macros, Visual Basic Editor, Form Controls, and more Excel geeky goodness.

VBA Macro to Copy the Current Worksheet

STEP 1: Go to Developer > Code > Visual Basic

STEP 2: Paste in your code and select Save. Close the window afterwards.

Sub CopyCurrentWorksheetToNewWorkbook()
'Copy the Current Worksheet
ThisWorkbook.ActiveSheet.Copy Before:=Workbooks.Add.Worksheets(1)
End Sub

STEP 3: Let us test it out!

Open the sheet containing the data, this is the one we want to copy to a new worksheet.

STEP 4: Go to Developer > Code > Macros

STEP 5: Make sure your macro is selected. Click Run.

With just one click, your active worksheet was copied to a new workbook!

Tips & Tricks

• Rename the macro to something more descriptive like ExportSheetToClient for easier reuse.
• Use ActiveSheet.Name in the macro to also rename the sheet dynamically if needed.
• Add a line like ActiveWorkbook.SaveAs "C:\YourPath\SheetCopy.xlsx" if you want it to be auto-saved.
• Test your macro on a dummy file first to avoid accidental overwrites.
• Use Application.DisplayAlerts = False if you want to suppress pop-ups during macro execution.

 

Frequently Asked Questions (FAQs)

Q1. Will this macro copy all hidden rows/columns and formatting?

Yes, this Excel macro will copy everything on the active sheet exactly as it is—including all hidden rows, hidden columns, formatting, formulas, and conditional formatting. It’s essentially cloning the sheet into a new workbook. This ensures that no layout or visual detail is lost in the process. However, if you’re relying on filters or data grouping, make sure those are visible before running the macro if you want them reflected in the output.

Q2. Does this macro copy charts, images, or objects in the worksheet?

Yes, any embedded objects on the worksheet—such as charts, SmartArt, shapes, buttons, or inserted images—will be copied along with the rest of the sheet. The macro doesn’t discriminate between data and design elements; it captures the full visual and functional layout. That makes it great for client reports or dashboards where visual elements are key. Just ensure nothing is linked to another workbook, as external links may not carry over fully.

Q3. Can I use this macro to copy multiple sheets at once?

By default, the macro only targets the currently active worksheet, so it won’t copy multiple sheets in one go. However, you can modify the code to specify multiple sheet names using Sheets(Array(“Sheet1”, “Sheet2”)).Copy. Alternatively, you can write a loop to iterate through selected sheets if the selection is dynamic. But if your goal is just a quick one-sheet extraction, the single-line macro is perfect.

Q4. Will this work if my Excel workbook is protected or shared?

In a protected workbook, the macro may run into permission issues, especially if the sheet is locked for editing or macro execution. You might be prompted for a password or face an error message, depending on the level of protection. In a shared workbook, Excel restricts certain features, including macros and VBA editing. It’s recommended to unshare or unprotect the workbook temporarily if you want the macro to run smoothly.

Q5. Can I run this macro in Excel Online or the Mac version?

Unfortunately, Excel Online does not support VBA macros, so this script won’t run there at all. On a Mac, Excel does support VBA to an extent, but there are known limitations, especially with certain commands and libraries. While this particular macro is simple and should work on most Mac versions, it’s always wise to test it before relying on it. For full compatibility and ease of use, running it on a Windows version of Excel is your best bet.