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.
Also Read: Selection Property VBA – Usage With Examples
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. ❤