Excel VBA: Copy Worksheet to Another Workbook Without Opening

We’ll use the following source file “Source”, saved in the .xlsm format. We’ll use VBA code to copy a worksheet named “Dataset” to another workbook.

Create a new workbook to work as a destination. We named the new Excel workbook “Destination.xlsm”.

Here’s the breakdown of the code:

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): This 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: will close the source workbook.

Running the Code

Change the Excel file location in the code accordingly to copy the sheet (for Workbooks.Open). Use the full location in your drive. Otherwise, it will return an error.
Run the VBA macro. You will see the following output:

Some Basic Excel VBA Macros You May Need to Copy Worksheets

Example 1 – Copy Worksheet to New Workbook Using VBA in Excel

Case 1.1 – Copy the Active Sheet to a New Workbook

Sub copy_worksheet_to_New_workbook()

ActiveSheet.Copy

End Sub

Case – 1.2 Copy Multiple Sheets to a New Workbook as Array

Sub copy_multiple_worksheets()

ActiveWindow.SelectedSheets.Copy

End Sub

Case 1.3 – Excel VBA to Copy Sheet to a New Workbook and Save

Use the following 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

Case 1.4 – VBA to Copy Multiple Sheets to a New Workbook as Values Only

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

Example 2 – VBA to Copy Worksheet in the Same Workbook in Excel

Case 2.1 – Copy a Worksheet Before Another Sheet

This code copies the “Dataset” sheet and pastes it before “Sheet2”:

Sub copy_before_sheet()

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

End Sub

Case 2.2 – Copy a Worksheet Before the First Sheet

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

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

The VBA will copy the sheet from one place to another:

Case 2.3 – Excel VBA to Copy a Sheet to the End

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

Case 2.4 – Excel VBA to Move a Sheet to the End of a Workbook

Use the following code:

Sub move_sheet_end()

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

End Sub

Case 2.5 – Excel VBA to Copy a Sheet and Rename the Copy Based on the Cell Value in B4

Use the following code:

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:

Example 3 – VBA to Copy a Worksheet to Another Workbook in Excel

Use 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 as the first sheet of the “Source.xlsm” workbook.

To place the sheet to the end, use the following VBA code instead:

Sub Copy_sheet_to_another_workbook()

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

End Sub

Case 3.1 – Excel VBA to Copy Worksheet to Another Workbook and Rename It via Input Box

Use the following code. You’ll get a dialog box asking you for the new name.

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

Case 3.2 – Excel VBA to Copy a Range to Another Sheet

The following VBA code copies the range B2:D9:

Sub copy_data_another_sheet()

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

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

End Sub

Change the range and workbook names per your needs.

Case 3.3 – Excel VBA to Copy a Range to Another Workbook

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

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

To 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