A.N.M. Mohaimen Shanto
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. And not only that, but we are also providing you with several VBA codes to copy worksheets, range to workbooks, etc. So, stay with us.
Download Practice Workbook
Download these practice workbooks. All the codes are in the Destination.xlsm file.
An Excel VBA Macro 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 to the destination file.
The following steps will describe the code better:
📌 Step 1: Create the Sub-procedure
Sub copy_worksheet()
End SubWe 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 SubThis 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 SubSource_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 SubSource_workbook.Close SaveChanges:=False: It will close the source workbook.
📌 Step 5: Run the VBA Macro
Now, run the VBA macro. And 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 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 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 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
Read More: VBA to Copy Worksheet to Another Workbook and Rename (5 Methods)
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
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 SubAfter running the code you will see the following:
Read More: How to Copy Sheet to End in Excel Using VBA (6 Methods)
Similar Readings:
- How to Copy Multiple Sheets in Excel to New Workbook (3 Methods)
- Excel VBA to Copy and Rename a Worksheet Multiple Times
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 Sub3.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 Sub3.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 SubIt will copy range from workbook Source and paste it to 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 SubCopy 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 files 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. There are more ways to copy a range to another sheet using VBA in Excel.
Read More: VBA Copy Sheet in Excel (13 Quick Ways)
💬 Things to Remember
✎ Your file location may not be the same as ours. So, change it according to your device.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge copy 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.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
