Excel VBA Worksheets.Copy Method to copy worksheet

This tutorial explains how to copy worksheets using Worksheets.Copy Method in Excel VBA, and demonstrate how to copy worksheet to another workbook.

You may also want to read:

Excel VBA Worksheets.Add Method to add new worksheet

Excel Workbooks Open Method and check if workbook open

Excel VBA Consolidate worksheets into one worksheet

Excel VBA Worksheets.Copy Method to copy worksheet

Excel VBA Worksheets.Copy Method is to copy worksheet in a workbook. You can copy worksheets from one workbook to another workbook, and specify to insert after or before specific worksheet.

Syntax of Excel VBA Worksheets.Copy Method

expression .Copy(Before, After)

Name	Description
Before	Optional. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.
After	Optional. The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.

If you don’t specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.

Example 1 – Copy worksheet in the same workbook

The below example copies “Sheet2” to the end of the worksheets, and rename it as “new worksheet”.

Public Sub cpy1()
    Sheets("Sheet2").Copy After:=Sheets(ThisWorkbook.Sheets.Count)
    ActiveSheet.Name = "new worksheet"
End Sub

The below example copies “Sheet2” and move to the first worksheet

Public Sub cpy2()
    Sheets("Sheet2").Copy Before:=Worksheets(1)
    ActiveSheet.Name = "new worksheet"
End Sub

Example 2 – Copy worksheet to another workbook

The below example copies worksheet “wbA ws1” to another workbook “workbook b” after worksheet “wbB ws1”

Public Sub cpywb()
    Workbooks("workbook a.xlsx").Worksheets("wbA ws1").Copy after:=Workbooks("workbook b.xlsx").Worksheets("wbB ws1")
End Sub

Example 3 – Copy worksheets to a new workbook

Sub cpyWS()
   Set Newbook = Workbooks.Add
   ThisWorkbook.Sheets("SheetA").Copy After:=Newbook.Sheets(Newbook.Sheets.Count)
   ThisWorkbook.Sheets("SheetB").Copy After:=Newbook.Sheets(Newbook.Sheets.Count)
End Sub

You may also read my another example to copy each worksheet to new workbook.

Example 4 – Copy worksheet from another closed workbook

This example was originally asked in Microsoft Community.

Question

Hi,

Need help with VBA Code.

I’ve searched and tried a few codes but have been having trouble finding something that works.

What I want to do:

1) I’m in an open and active workbook

2) I want to insert and new sheet at the end of all the sheets in the open workbook

3) The sheet I want to insert is from a closed workbook with several worksheets.

Info, Please use in example code, appreciated.

1) location:   “C:\Users\Xxxx\Documents\Xxxx\Target File.xlsx”

Answer

Public Sub cpy()
     Application.ScreenUpdating = False
     Set fileB = Workbooks.Open("C:\Users\Xxxx\Documents\Xxxx\Target File.xlsx")
     fileB.Sheets("Sheet2").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
     fileB.Close
     Application.ScreenUpdating = True
 End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff837784.aspx