VBA to Copy Worksheet to Another Workbook and Rename (5 Methods)

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.


Download Workbook

You can download the free practice Excel workbook from here.


5 Methods to Copy One Worksheet to Another Workbook and Rename It with VBA in Excel

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 Sub

Your 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.

VBA to Copy One Worksheet to Another Excel Workbook and Rename It in Excel

  • 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.

Result of VBA to Copy One Worksheet to Another Excel Workbook and Rename It in Excel

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 hardcode 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 Sub

Your code is now ready to run.

VBA to Copy One Worksheet to Another Excel Workbook and Rename It from User in Excel

  • 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).
  • Lastly, press OK.

Notice in the image below.

Result of VBA to Copy One Worksheet to Another Excel Workbook and Rename It from User in Excel

In the end, there is a newly inserted sheet named New Sheet in the Duplicate workbook.

Read More: VBA to Copy Excel Worksheet to Another Workbook without Formulas


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” that is in Cell B7. So, we make Cell B7 the active cell.
  • Second, 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
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 Sub

Your code is now ready to run.

VBA to Copy One Worksheet to Another Excel Workbook and Rename It Based on Cell Value in Excel

  • 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.

Result of VBA to Copy One Worksheet to Another Excel Workbook and Rename It Based on Cell Value in Excel

There is a new sheet named Dean in the Duplicate workbook.

Read More: VBA Copy Sheet in Excel (13 Quick Ways)


Similar Readings:


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.
  • Second, 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 Sub

Your code is now ready to run.

VBA to Copy One Worksheet to Another Excel Workbook and Rename It Based on Cell Reference in Excel

  • Next, Run this code. This piece of code holds the cell reference number B4 and Cell B4 has the value First Name in it.

Result of VBA to Copy One Worksheet to Another Excel Workbook and Rename It Based on Cell Reference in Excel

Now, consider the above image. This image is showing us that the Duplicate workbook now has the copied sheet with a new sheet named First Name.

Read More: How to Copy Excel Sheet to Another Sheet with Same Format


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 close. 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 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.
  • Second, 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 Function

Your code is now ready to run.

VBA to Check then Copy One Worksheet to Another Excel Workbook and Rename It in Excel

  • 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.

Result of VBA to Check then Copy One Worksheet to Another Excel Workbook and Rename It in Excel

Finally, we get the copied Dataset sheet in the Duplicate workbook with the sheet name Duplicate Sheet.

Read More: How to Copy Excel Sheet to Another Sheet (5 Ways)


Things to Remember

  • Methods 1 to 4 requires 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.

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.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo