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.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)
|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.
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”
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