Copy Worksheet using VBA – Examples

In this article, we will learn to copy worksheet using VBA. Copying or Duplicating the worksheets is very easy and can be automated using macros to save you time.

So let us start learning.

Copy Sheet To New Workbook

The copy method of the Worksheet Object is used to copy the worksheet using VBA. We can also specify the before or after argument of this method to specify the destination to paste the worksheet. If we do not specify the argument, the new workbook is automatically created and the worksheet is copied to it.

Using this Sub procedure we can copy the active sheet of the current workbook and then its copy will be created in a new workbook.

Sub copyToNewWorkbook()
'copy the active sheet and then paste to a new workbook
ActiveSheet.Copy
End Sub

This macro created a new workbook named Book2 having the worksheet copied from Book1.

Copy Selected Worksheets To New Workbook

We can select multiple worksheets by holding the shift key and clicking on the sheet tab to select it and then run the following Sub Procedure to copy them to a new Workbook.

Sub copySelectedWorksheets()
'copy the selected sheets to a new workbook
ActiveWindow.SelectedSheets.Copy
End Sub

Let us say here we have three worksheets named Jan, Feb, and March. First, we select the sheets named Jan and March and then run the Macro.

Copy the Worksheet to Another Workbook

In this section of the blog, we will learn to copy the worksheet to an opened workbook, We can refer to the opened workbook with the help of Workbooks Collection.

Copy Worksheet To Beginning Using VBA

We can copy the active sheet and then paste it before the first sheet of the opened workbook using the Before argument of Copy method.

The source worksheet will be copied before the Worksheet specified in Before Argument.

Let us suppose we have opened two workbooks named Book1 and Book2. You need to paste the Sub procedure in a New Module in Book1.

Sub pasteToBegOfAnotherWorkbook()
'copy worksheet at beginning
ActiveSheet.Copy before:=Workbooks("Book2").Sheets(1)
End Sub

You can see the workbook name on the title bar.

Copy Worksheet To End Using VBA

You can copy the worksheet to the end. This can be done by mentioning the After argument. We will specify the last worksheet using the Sheets Collection. We can get the index of last worksheet by using the Count property of the Sheets Collection.

Use the following Sub Procedure to copy to the end of another workbook Book2.

Sub pasteToEndOfAnotherWorkbook()
'copy worksheet at end
ActiveSheet.Copy after:=Workbooks("Book2").Sheets(Workbooks("Book2").Sheets.Count)
End Sub

Copy and Rename the Worksheet

Excel automatically assigns names to the copied worksheet. We can change this name with the help of the name property of the copied worksheet.

In this Sub Procedure, we will copy the Active Sheet of the workbook and paste it to the end of the same workbook with its name changed to CopiedWorkbook

Sub copyAndRenameWorksheet()
'copy worksheet at the end of workbook
ActiveSheet.Copy after:=Sheets(Sheets.Count)
'copied worksheet is now active sheet
'change the name of copied worksheet
ActiveSheet.Name = "Copied Worksheet"
End Sub

When you run this Sub Procedure, the worksheet is copied at the end.

You can also use the Input Box to ask the user to enter the name of the copied worksheet.

Copy Worksheet To a Closed Workbook

We can copy a worksheet to a workbook that is stored on the PC without actually opening it. We will use the GetOpenFilename method of the Applications object to open the windows file dialog box and select the excel workbook to which we want to copy the workbook.

You can use the following macro to copy the active sheet to a closed workbook on your PC.

Sub copyToClosedWorkbook()
'copy worksheet to a closed workbook
Dim copiedWS As Worksheet
Dim closedWB As Workbook
Dim filename As String
'select a file from windows file dialog box and assing the name of file to filename variable
filename = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
'if filename is not empty then
If filename <> "" Then
'stop screen updating
Application.ScreenUpdating = False
'set the worksheet to copy
Set copiedWS = Application.ActiveSheet
'set the closed workbook to copy to
Set closedWB = Workbooks.Open(filename)
'copy the worksheet at the end of closed workbook
copiedWS.Copy after:=closedWB.Sheets(closedWB.Sheets.Count)
'close the workbook
closedWB.Close
'turn on the screen updating
Application.ScreenUpdating = True
End If
End Sub

When you run the above Sub procedure, the Windows File dialog box appears from where you select the Excel file to copy the active worksheet at the end of the selected workbook.

Copy Worksheets Multiple Times

We can create multiple duplicates of a worksheet to get multiple copies of data in the worksheet and then make changes to it.

You can use the following macro to make multiple duplicates of the active worksheet in the same workbook.

Sub createMultipleCopiesOfWorksheet()
'create multiple copies of a worksheet
Dim n As Integer
n = InputBox("Enter the number of duplicates")
If n <> 0 Then
For i = 1 To n
ActiveSheet.Copy after:=Sheets(Sheets.Count)
Next i
End If
End Sub

This brings us to an end.

Thank you for reading. ❤