Excel VBA: Copy Worksheet to Another Workbook Without Opening

In Microsoft Excel, using VBA macros can solve a lot of problems with ease. A crucial procedure of VBA macros is to copy worksheets to another workbook or workbooks in Excel. We can use these codes to copy data, copy sheets, or copy workbooks in Excel. In this tutorial, you will learn to copy a worksheet to another workbook without opening it in Excel using VBA macros. Not only that, but we are also providing you with several VBA codes to copy worksheets, range to workbooks, etc. So, stay with us.

Excel VBA to Copy Worksheet to Another Workbook Without Opening It

Now, first thing first. In this section, we will show you the VBA macros to copy a worksheet to another workbook without opening it.

First, look at our Source file:

Here, you can see we have a workbook “Source.xlsm”. Here, we have a sheet called “Dataset”. Now, using the VBA we will copy it to another workbook without opening it.

Create a new workbook to work as a destination. We named the Excel workbook “Destination.xlsm”. We will copy the dataset from the source file and paste it into the destination file.

The following steps will describe the code better:

📌 Step 1: Create the Sub-procedure

Sub copy_worksheet()

End Sub

We will execute our code here.

📌 Step 2: Open the Source Workbook Where Your Sheet Is Located

Sub copy_worksheet()

Application.ScreenUpdating = False

    Set Source_workbook = Workbooks.Open("D:\SOFTEKO\83-excel vba copy worksheet to another workbook without opening\Source.xlsm")

End Sub

This code will open the source workbook. We disabled the Application.ScreenUpdating to run the process in the background.

📌 Step 3: Copy the Sheet from Source and Paste in Current Workbook

Sub copy_worksheet()

Application.ScreenUpdating = False

    Set Source_workbook = Workbooks.Open("D:\SOFTEKO\83-excel vba copy worksheet to another workbook without opening\Source.xlsm")

    Source_workbook.Sheets("Dataset").Copy Before:=ThisWorkbook.Sheets(1)

End Sub

Source_workbook.Sheets(“Dataset”).Copy Before:=ThisWorkbook.Sheets(1): It will copy the sheet from the source and paste it into the current workbook.

📌 Step 4: Close the Source Workbook

Sub copy_worksheet()

Application.ScreenUpdating = False

    Set Source_workbook = Workbooks.Open("D:\SOFTEKO\83-excel vba copy worksheet to another workbook without opening\Source.xlsm")

    Source_workbook.Sheets("Dataset").Copy Before:=ThisWorkbook.Sheets(1)

    Source_workbook.Close SaveChanges:=False

Application.ScreenUpdating = True

End Sub

Source_workbook.Close SaveChanges:=False: It will close the source workbook.

📌 Step 5: Run the VBA Macro

Now, run the VBA macro. You will see the following output:

As you can see, our destination Excel file got the same sheet and dataset from the source workbook. Our source file was closed. And we paste the dataset without opening the workbook. So, this code works file.

Remember, you must create those Excel files or workbooks before you copy and paste. Change the Excel file location according to your PC to copy the sheet. Otherwise, it will return an error.

Some Basic Excel VBA Macros You May Need to Copy Worksheet

In the upcoming sections, I will provide you with several crucial VBA codes to copy a sheet or multiple sheets. By using the following codes, you can copy and paste those sheets into the current workbook, new workbook, or another workbook that you saved. I recommend you learn and apply all these VBA codes to your Excel workbook. It will undoubtedly develop your Excel VBA knowledge.

1. Copy Worksheet to New Workbook Using VBA in Excel

Now, you are working with your current workbook and copy sheet from one workbook to another. But, you want to copy it to another new workbook using VBA macros. The following sections will provide you with exactly that.

1.1 Copy Active Sheet to New Workbook

Now, to copy the Active sheet to another new workbook:

Sub copy_worksheet_to_New_workbook()

ActiveSheet.Copy

End Sub

1.2 Copy Multiple Sheets to New Workbook Array

Now, to copy multiple sheets to a new workbook array, you have to select the multiple sheets. Copy them using the following VBA macro:

Sub copy_multiple_worksheets()

ActiveWindow.SelectedSheets.Copy

End Sub

This VBA code will copy multiple sheets at once in Excel.

1.3 Excel VBA Copy Sheet to New Workbook and Save

Now, you want to copy the “Dataset” sheet to a new workbook. You can create a new workbook and paste the sheet there using the following VBA code:

Sub copy_sheet_and_save()

    Dim file_name As String

    Dim file_path As String

    Dim new_workbook As Workbook

    file_path = "D:\SOFTEKO\83-excel vba copy worksheet to another workbook without opening"

    file_name = "Destination" & Format(Date, "ddmmyy") & ".xlsm"

    Set new_workbook = Workbooks.Add

    ThisWorkbook.Sheets("Dataset").Copy Before:=new_workbook.Sheets(1)

    If Dir(file_path & "\" & file_name) <> "" Then

        MsgBox "File " & file_path & "\" & file_name & " already exists"

    Else

        new_workbook.SaveAs Filename:=file_path & "\" & file_name

    End If

End Sub

This VBA code will successfully create a new Excel file and copy the current worksheet to it without opening it.

1.4 Vba Copy Multiple Sheets to New Workbook as Values

Previously, we showed you how to copy multiple sheets with a simple code. Now, your workbook may contain various sheets with a lot of formulas. You want to copy those sheets to another workbook but without the formula. Here, the following code will do that. But, instead, it will copy the used range of the current workbook to a new one. Two workbooks will almost look the same.

Sub copy_worksheets_to_Workbook_values()

    Dim current_workbook_name As Workbook

    Dim current_workbook_name As String

    Dim New_File As String

    Set current_workbook_name = ThisWorkbook

    current_workbook_name = ThisWorkbook.FullName

    Application.DisplayAlerts = False

    Dim ws As Worksheet

    For Each ws In current_workbook_name.Worksheets

        ws.UsedRange.Copy

        ws.UsedRange.PasteSpecial xlPasteValues, _

            Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Next

    New_File = ThisWorkbook.Path & "\" & "worksheet2.xlsx"

    current_workbook_name.SaveAs New_File, XlFileFormat.xlOpenXMLWorkbook

    Workbooks.Open current_workbook_name

    current_workbook_name.Close

    Application.DisplayAlerts = True

End Sub

2. VBA to Copy Worksheet in Same Workbook in Excel

In this section, we will copy the sheet and perform some operations in the same workbook. If you don’t want to perform VBA macros to another new workbook, try these codes.

2.1 Copy Worksheet Before Another Sheet

Here, by using the following VBA macro, you can copy the “Dataset” sheet and paste it before “Sheet2”.

Sub copy_before_sheet()

Sheets("Dataset").Copy Before:=Sheets("Sheet2")

End Sub

2.2 Copy Worksheet Before First Sheet

If you have multiple sheets and you don’t want to specify them by their name, you can use the sheet numbers.

Here, our “Dataset” sheet is sheet number one. We will copy the “Sheet2” before the first sheet in the same workbook using the following VBA code:

Sub copy_before_first_sheet()

Sheets("Sheet2").Copy Before:=Sheets(1)

End Sub

After running the code, the VBA will copy the sheet from one place to another:

2.3 Excel VBA to Copy Sheet to End

Similar to the previous method, you can copy a worksheet and paste it to the end or after the last sheet in the current workbook.

The following code will copy the “Dataset” sheet and paste it to the end:

Sub copy_sheet_end()

Sheets("Dataset").Copy After:=Sheets(Sheets.Count)

End Sub

Read More: How to Copy Sheet to End in Excel Using VBA

2.4 Excel VBA to Move a Sheet

If you don’t want to copy and paste a sheet but move a sheet, try the following code:

Sub move_sheet_end()

Sheets("Dataset").Move After:=Sheets(Sheets.Count)

End Sub

This VBA macro will move the Dataset sheet to the end in the current Excel workbook.

2.5 Excel VBA to Copy Sheet and Rename Based on Cell Value

By the following VBA code, we will copy the “Dataset” sheet. After that, we will paste it to the end. But, will rename the sheet name based on a cell value. We will do it within the same workbook.

Sub Copy_rename_sheet()

Dim sh As Worksheet

Set sh = Sheets("Dataset")

sh.Copy After:=Sheets(Sheets.Count)

If sh.Range("B4").Value <> "" Then

    ActiveSheet.Name = sh.Range("B4").Value

    End If

    sh.Activate

End Sub

After running the code you will see the following:

3. VBA to Copy Worksheet to Another Workbook in Excel

If you want to copy a worksheet to another workbook try the following code:

Sub Copy_sheet_to_another_workbook()

Sheets("Dataset").Copy Before:=Workbooks("Source.xlsm").Sheets(1)

End Sub

This VBA code will copy the sheet “Dataset” from the “Destination” workbook and paste it to the first place of “Source.xlsm” workbook.

To place the sheet to the end, try the following VBA macro:

Sub Copy_sheet_to_another_workbook()

Sheets("Dataset").Copy After:=Workbooks("Source.xlsm").Sheets(Workbooks("Souce.xlsm").Sheets.Count)

End Sub

3.1 Excel VBA to Copy Worksheet to Another Workbook and Rename

If you want to copy a sheet to another workbook and rename it, try the following code:

Sub cpy_to_workbook_rename()

 Dim ws As Worksheet
 Set ws = Sheets("Dataset")
 Dim n As String

 n = InputBox("Please Provide a New Name: ")
 ws.name = n
 ws.Copy Before:=Workbooks("Source.xlsm").Sheets(1)

End Sub

3.2 Excel VBA to Copy Range to Another Sheet

To copy a range from another sheet, use the following VBA code:

Sub copy_data_another_sheet()

Worksheets("Dataset").Range("B2:D9").Copy _

Worksheets("Sheet2").Range("B2:D9")

End Sub

3.3 Excel VBA to Copy Range to Another Workbook

Now, you can copy data or range from a workbook and paste it into another workbook. Using the following VBA macro you can copy a range from a sheet in three scenarios:

When Your Workbooks Are Open but Aren’t Saved Yet:

Sub copy_data()

Workbooks("Source").Worksheets("Dataset").Range("B2:D9").Copy _
Workbooks("Destination").Worksheets("Sheet2").Range("B2:D9")

End Sub

It will copy the range from the workbook “Source” and paste it to the “Destination” workbook’s sheet number 2.

When Your Workbooks Are Open and Saved:

Sub copy_data2()

Workbooks("Source.xlsm").Worksheets("Dataset").Range("B2:D9").Copy _
Workbooks("Destination.xlsm").Worksheets("Sheet2").Range("B2:D9")

End Sub

Copy a Range to a Worksheet in Another Closed Workbook

Sub copy_data3()

Workbooks.Open "D:\SOFTEKO\Copy Sheet\Destination.xlsm"

Workbooks("Source").Worksheets("Dataset").Range("B2:D9").Copy _
Workbooks("Destination").Worksheets("Sheet2").Range("B2:D9")

Workbooks("Destination").Close SaveChanges:=True

End Sub

Change the Excel file name and folder location according to your device. In this way, you can Copy Range from a worksheet to Another Workbook using VBA in Excel.

💬 Things to Remember

✎ Your file location may not be the same as ours. So, change it according to your device.

Download Practice Workbook

Download these practice workbooks. All the codes are in the “Destination.xlsm” file.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to copy the worksheet to another workbook without opening using VBA codes in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Keep learning new methods and keep growing!