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.

In this Topic:

VBA Copy Worksheet

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.

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: March 8th, 2015Categories: VBATags: , , ,

Share This Story, Choose Your Platform!

About the Author: Valli

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.