REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

Copy worksheet method 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.

VBA Copy Method Excel Worksheet Object

When we use Copy Worksheet method in VBA?

If we want to Copy worksheet from one workbook to another existing workbook or new workbook we will use Copy method of worksheet object. 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 worksheet method in VBA.

VBA Copy Worksheet Method- Syntax

Here is the 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
In the above Syntax Worksheet represents object and Copy represents metod of Worksheet Object.

VBA Copy Worksheet Method –Before: Example 1

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 Method -After: Example 2

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 Method- Instructions

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

  1. Open an Excel Worksheet
  2. Press Alt+F11 :This will Open the VBA Editor. Otherwise, you can open it from the Developer Tab
  3. Insert a Module from Insert Menu
  4. Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
  5. Save the file as macro enabled Worksheet
  6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line and check how the Worksheet copied .
Effortlessly Manage Your Projects and Resources
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.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Last Updated: March 2, 2023

Leave A Comment