When we are dealing with multiple Excel sheets, sometimes we need to copy one worksheet to another workbook and provide a new name to it for better understanding. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to copy one worksheet to another workbook and rename it in Excel with the VBA macro.
Excel VBA to Copy Worksheet to Another Workbook and Rename: 5 Methods
In this section, we will show you how to copy one worksheet to another workbook and rename it with 5 effective methods in Excel VBA.
Consider the following image. We have a workbook named Existing. And there is a sheet named Dataset in that workbook.
Next, we have another workbook named Duplicate (see the following image).
What we are going to do is, we will copy the Dataset sheet from the Existing workbook and paste it into the Duplicate workbook with a new sheet name.
1. Embed VBA to Copy One Worksheet to Another Excel Workbook and Rename It
Here, you will learn how to rename a worksheet with a predefined name in the VBA code.
Steps:
- In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- In the pop-up code window, from the menu bar, click Insert -> Module.
- Now, copy the following code and paste it into the code window.
Public Sub CopyAndRename()
Workbooks("Existing.xlsm").Worksheets("Dataset").Copy After:=Workbooks("Duplicate.xlsx").Worksheets(Sheets.Count)
On Error Resume Next
Worksheets("Dataset").Name = "Copied Sheet" 'Set new name
End SubYour code is now ready to run.
Here,
- “Copied Sheet” is the new sheet name of the copied Dataset sheet. You can rename it with any name that you want.
- Then, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.
Look at the following image.
Finally, the Dataset sheet from the Existing workbook is now copied in the sheet named Copied Sheet sheet in the Duplicate workbook.
Read More: How to Copy Worksheet to Another Workbook Using VBA
2. VBA Macro to Duplicate Worksheet to Another Workbook and Rename It by User
In the previous section, we learned how to copy and rename a sheet by hardcoding the name inside the macro. But what if, you want to name the sheet after the code execution?
The steps to do that are given below.
Steps:
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Public Sub CopyAndRenameUser()
Dim iName As String
On Error Resume Next
iName = InputBox("Enter the New Name for the Copied Sheet")
If iName <> "" Then Workbooks("Existing.xlsm").Worksheets("Dataset").Copy After:=Workbooks("Duplicate.xlsx").Worksheets(Sheets.Count)
On Error Resume Next
Worksheets("Dataset").Name = iName
End If
End SubYour code is now ready to run.
- Next, Run the code as shown above and there will be a pop-up input box asking you for a new name.
- Now, enter the name that you want your copied sheet to have (we renamed our sheet as New Sheet).
- Press OK.
Notice in the image below.
In the end, there is a newly inserted sheet named New Sheet in the Duplicate workbook.
3. Macro to Copy Worksheet and Rename Based on Cell Value in Excel
What if, you want to name your sheet based on the cell value that the sheet has? It is a very convenient way when you want to have a new worksheet based on some specific value, such as the header value or any other cell value from the previous sheet.
Let’s learn how to do that.
Steps:
- First, select the cell based on what you want to rename the sheet to make it the active cell. For instance, we want to rename the copied sheet with the name “Dean” which is in Cell B7. So, we make Cell B7 the active cell.
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- In the code window, copy the following code and paste it.
Public Sub CopyAndRenameByCellValue()
Dim iName As String
On Error Resume Next
iName = InputBox("Rename as it is? Press OK. Otherwise Enter New Name", "Copy and Rename Worksheet", ActiveCell.Value)
If iName <> "" Then
Workbooks("Existing.xlsm").Worksheets("Dataset").Copy After:=Workbooks("Duplicate.xlsx").Worksheets(Sheets.Count)
On Error Resume Next
Worksheets("Dataset").Name = iName
End If
End SubYour code is now ready to run.
- Next, Run this piece of code and look at the following picture.
- Value from the cell (Cell B7) that we had as our active cell, Dean, is automatically predicted as the new name.
- Now, press OK.
The result can be seen in the picture below.
There is a new sheet named Dean in the Duplicate workbook.
Read More: How to Copy Sheet to End in Excel Using VBA
4. Insert VBA Code to Duplicate Spreadsheet and Rename It Specified by Cell Reference
Unlike the previous section, this time we will hardcode the cell reference number inside the code instead of keeping that cell as an active cell.
Steps:
- First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Copy the following code and paste it into the code window.
Public Sub CopyAndRenameByCellRef()
Dim iSheet As Worksheet
Set iSheet = ActiveSheet
Workbooks("Existing.xlsm").Worksheets("Dataset").Copy After:=Workbooks("Duplicate.xlsx").Worksheets(Sheets.Count)
If iSheet.Range("B4").Value <> "" Then
On Error Resume Next
Worksheets("Dataset").Name = iSheet.Range("B4").Value
End If
iSheet.Activate
End SubYour code is now ready to run.
- Next, Run this code. This piece of code holds the cell reference number B4 and Cell B4 has the value First Name in it.
Now, consider the above image. This image shows us that the Duplicate workbook now has the copied sheet with a new sheet named First Name.
Read More: Excel VBA to Copy Multiple Sheets to New Workbook
5. Check Whether the Workbook is Close or Open then Copy Sheet and Rename in Excel
All the previous methods require the workbooks must be open when executing the code. But in this section, you will get the code that will first check whether the workbook is open or closed. If the workbook is open, then it will copy the sheet and rename it. But if the workbook is closed, then it will open the workbook first and then perform the rest of the task such as copying the sheet and renaming it.
This method is the most effective method while performing the copying and renaming worksheet tasks in Excel.
Steps:
- First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Copy the following code and paste it into the code window.
Sub CopyAndRenameSheetToWorkbook()
Dim iFile
'To check whether the workbook is open or not
iFile = WorkbookOpenOrClose("H:\ExcelDemy\Excel Folder\Existing.xlsm")
If iFile = True Then
MsgBox "File is Open"
Else
MsgBox "File is Closed"
End If
'To open the workbook if closed
If iFile = False Then
Workbooks.Open FileName:="H:\ExcelDemy\Excel Folder\Existing.xlsm"
End If
'To copy the worksheet Dataset to Duplicate workbook after the sheet First Name
Sheets("Dataset").Copy After:=Workbooks("Duplicate.xlsx").Sheets("First Name")
'To assign the new name via inputbox to rename the copied sheet
Sheets(Sheets.Count).Name = InputBox("Rename the Copied Sheet")
End Sub
'This function checks whether the workbook is open or not.
'If open, returns True. If close, returns False.
'Else, capture the error number of occured run-time error.
Function WorkbookOpenOrClose(FileName As String)
Dim iFreeFile As Integer
Dim iError As Integer
On Error Resume Next 'To turn off error checking
iFreeFile = FreeFile() 'To get a free file number by the inbuilt function
'To open FileName For Input Lock Read As #iFreeFile
'To open the file and lock it
Close iFreeFile 'To close the file
iError = Error 'To capture the error number
On Error GoTo 0 'To turn on error checking
'To Find which error happened
Select Case iError
'No error found
'File is closed
Case 0: WorkbookOpenOrClose = False
'Error message for "Permission Denied"
'File is already opened by another user
Case 70: WorkbookOpenOrClose = True
'Another error occurred
'To capture the error number for further action
Case Else: Error iError
End Select
End FunctionYour code is now ready to run.
- Next, Run this code.
- A pop-up message box will appear, showing you the result of whether your file is open or closed.
- Now, press OK.
- Later, another pop-up input box will appear, asking you for a new name.
- Now, enter the name that you want your copied sheet to have (we renamed our sheet as Duplicate Sheet).
- Lastly, press OK.
The result is shown in the picture below.
Finally, we get the copied Dataset sheet in the Duplicate workbook with the sheet name Duplicate Sheet.
Things to Remember
- Methods 1 to 4 require your workbooks to be opened. When executing the macro codes shown in those methods, don’t forget to keep both the source and destination workbooks open.
- While your workbooks are saved then write the file name with the file type inside the code. When the workbooks are not saved, then write only the file name without the type of the file. For example, if your workbook is saved, then write “Duplicate.xlsx”, but if the workbook is not saved, then write “Duplicate” inside the code.
Download Workbook
You can download the free practice Excel workbook from here.
Conclusion
This article showed you how to copy one worksheet to another workbook and rename it in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
