VBA Copy Worksheet Explained with Examples

Home/VBA/Copy Worksheet

Copy worksheet in VBA is used to Copy the worksheet from one location to another location in the same workbook or another new workbook or existing workbook. Where Copy is a method of Worksheet object. Please find the more information about Copy Worksheet(s) in the following chapter. Sometimes we may want to Copy worksheet in the active workbook at the beginning of the worksheet or in between worksheets or at the end of worksheet. According to our requirement we can Copy the worksheets using Copy method of Worksheet object in VBA.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


In this Topic:

VBA Copy Worksheet: Syntax

Please find the below syntax to Copy Worksheet using VBA.

Sheets(“Worksheet Number”).Copy([Before], [After])

Where
Before: It’s an Optional parameter. The worksheet will be Copied to before the specified worksheet. Then we can’t specify after parameter.
After: It’s an Optional parameter. The worksheet will be Copied to after the specified worksheet. Then we can’t specify after parameter.
Note: If you don’t specify either before or after, Excel will create new workbook that contains the Copied worksheet

VBA Copy Worksheet: Using Before

Please find the below example, It will show you how to Copy the Worksheet to the beginning.

Sub CopySheet_Beginning()
Worksheets("Sheet3").Copy Before:=Worksheets(1)
End Sub

In the above example we are Copying the Worksheet named ‘Sheet3’ to the beginning of the worksheet. Where ‘1’ represents the Worksheet index number (Nothing but first available sheet in the workbook).

Sub CopySheet_Beginning1()
ActiveSheet.Copy Before:=Worksheets(1)
End Sub

In the above example we are Copying the active worksheet to the beginning of the worksheet.

VBA Copy Worksheet: Using After

Please find the below example, It will show you how to Copy the Worksheet at the end of the available worksheets.

Sub CopySheet_End()
Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count)
End Sub

In the above example we are copying the Worksheet named ‘Sheet3’ to the end of the worksheet. Where ‘Worksheets.Count’ represents the number of available worksheets in the workbook

Sub CopySheet_End()
ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
End Sub

In the above example we are Copying the active worksheet to the end of the worksheet.

VBA Copy Worksheet: Before Specified Worksheet

Please find the below example, It will show you how to Copy the Worksheet either before or after the specified worksheet.

Sub CopySheet_Before()
Worksheets("Sheet2").Copy Before:=Sheets("Sheet5")
End Sub

Please find the above example, we are Copying ‘Sheet2’ to the before ‘Sheet5’.

Sub CopySheet_Before()
ActiveSheet.Copy Before:=Sheets("Sheet5")
End Sub

In the above example, we are Copying active sheet to the before ‘Sheet5’.

Sub CopySheet_After()
Worksheets("Sheet2").Copy After:=Sheets("Sheet5")
End Sub

Please find the above example, we are Copying ‘Sheet2’ to the after ‘Sheet5’.

Sub CopySheet_After()
ActiveSheet.Copy After:=Sheets("Sheet5")
End Sub

In the above example, we are Copying active sheet to the after ‘Sheet5’.

VBA Copy Worksheet: To New Workbook

Please find the below example, It will show you how to Copy the Worksheet named ‘Sheet1’ to new workbook.

Sub CopySheet_NewWorkbook()
Sheets("Sheet1").Copy
End Sub

Please find the below example, It will Copy the active worksheet to new workbook.

Sub CopySheet_NewWorkbook()
ActiveSheet.Copy
End Sub

VBA Copy Worksheet: To Specific Workbook

Please find the below example, It will show you how to Copy the Worksheet named ‘Sheet1’ to Specific workbook before ‘Sheet3’.

Sub CopySheet_SpecificWorkbook ()
Sheets("Sheet1").Copy Before:=Workbooks("YourWorkbookName.xls").Sheets(“Sheet3”)
End Sub

Please find the below example, It will Copy the active worksheet to Specific workbook after ‘Sheet3’.

Sub CopySheet_SpecificWorkbook ()
ActiveSheet.Copy After:=Workbooks("YourWorkbookName.xls"). Sheets(“Sheet3”)
End Sub

VBA Copy Worksheet Method- Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

Step 1: Open an Excel Worksheet
Step 2: Press Alt+F11 to Open VBA Editor
Step 3: Insert a Module from Insert Menu
Step 4: Copy the above code for activating a range and Paste in the code window (VBA Editor)
Step 5: Save the file as macro enabled Worksheet
Step 6: Press ‘F5′ to run it or Keep Pressing ‘F8′ to debug the code line by line and have a look how the Worksheet(s) Copying in the workbook.

LIMITED TIME OFFER
By |March 8th, 2015|VBA|5 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

5 Comments

  1. Venkat August 29, 2015 at 5:07 PM - Reply

    Dear Mr.P N Rao ! What is the Basic difference b/w a macro and procedure..??

    • PNRao August 30, 2015 at 3:15 AM - Reply

      Hi Venkat,
      A Marco is a set of statements which generally created for completing repetitive simple tasks ( without programming concepts like data types, logical expressions, conditional statements etc.)

      Where as Procedures are written using programming concepts.

      Example: In MS Excel, We can use the Macro recorder to automate simple tasks. Application generates simple statements, where you can’t see any logical statements, etc. But, a programmer writes the procedures using programming concepts, which is more reliable and generalized.

      Hope this clarifies.
      Thanks-PNRao!

  2. shahnas November 6, 2015 at 10:27 AM - Reply

    hi,

    how to copy one excel sheet(matser file) in to another excel work book( which contain 31 sheets) by date basis

  3. Saranya November 10, 2015 at 7:50 PM - Reply

    I have several sheets in a source workbook and I need to copy each sheets’s data to another workbook which has got the same name as the sheet name in the source workbook. I have workbooks as many as the number of sheets in the source workbook. How to copy the data from sheets to workbooks in sheet2

  4. Chait July 1, 2016 at 3:13 PM - Reply

    i have 2 workbooks in a location C:\new\folder. I want sheet 1 from wb1, sheet3 from wb2 and place it one after the other in another existing workbook wb3 in the same location when i click a button in wb3. can you help me with a code?

Leave A Comment