6 Quick Ways to Copy Range to Another Sheet with Excel VBA

Sometimes we need to copy range to another sheet using VBA in Excel. In this article, we are going to show you Excel VBA copy range to another sheet with 6 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.

📂  Download Excel File

Download free workbook from here:

Learn to Copy Range to Another Sheet Using VBA in Excel with These 6 Ways

While writing this article, “Excel VBA Copy Range to Another Sheet ”, we’ve considered a dataset that contains approximately 5 columns and 12 rows. In this dataset, we added Name, Patient ID, City, and Country to make you better understand. But if you want, you can change the entities and put your own values.

If you want to copy the range to another sheet in Excel format then you can do it very easily. You just need to follow the given steps and you will achieve the process.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, go to the Dataset sheet.
• Secondly, press Alt+F11.
• Now the VBA window will appear to you.
• Then insert a Module on that window.

• Thirdly, write the below code on that Module.

Code

Sub Copying_Range1()
Range("B3:E12").Copy Worksheets("Format").Range("B3:E12")
End Sub

• Now save the code and go back to the worksheet.
• Fourthly, go to the View tab
• Tap Macros and View Macros.

• Fifthly, select the Macro name as Copying_Range1.
• Now press Run.

• Finally, you will see the range copied to another sheet in Excel with the format to the sheet named Format which I created.

Lastly, using this VBA code to Excel copy range to another sheet with format is a very easy procedure to work on.

You can also copy range to another sheet in Excel using VBA without format. It is not a difficult task to do. You will need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select the Dataset worksheet.
• Secondly, tap Alt+F11.
• Now the VBA window will come to you.
• Then insert a Module on that window.

• Thirdly, paste the below code on that Module.

Code

Sub Copying_Range1()
Range("B3:E12").Copy
Sheets("Beyond Format").Range("B3").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

• Now save the code and you need to go back to the worksheet.
• Fourthly, select the View tab.
• Press the Macros and View Macros.

• Fifthly, select the Macro name as Copying_Range1.
• Then select Excel VBA Copy Range to Another Sheet.xlsm as Macros in.
• Finally press Run.

• Consequently, you will find the range copied to another sheet in Excel without format to the sheet named Beyond Format which I created.

Undoubtedly, this is a very simple and handy technique.

If you intend to copy range to another sheet in Excel applying VBA with format and column width then this part is for you. You will need to maintain the following steps to achieve this.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the Dataset sheet.
• Belatedly, tap Alt+F11.
• Now you will see the VBA window.
• Then insert a Module on that window as well.
• Thirdly, you have to paste the below code on that Module.

Code

Sub Copying_Range_with_FormatAndColumnWidth()
Range("B3:E12").Copy Destination:=Sheets("Format and ColumnWidth").Range("B3")
Range("B3:E12").Copy
Sheets("Format and ColumnWidth").Range("B3").PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

• Now you will have to save the code and you need to go back to the worksheet.
• Fourthly, click on the View tab.
• Then press the Macros and View Macros.

• Fifthly, select the Macro name as Copying_Range_with_FormatAndColumnWidth.
• Then you need to select Excel VBA Copy Range to Another Sheet.xlsm as Macros in.
• Finally, you need to press Run.

• Consequently, you will see the range copied to another sheet in Excel to the sheet name Format and ColumnWidth which I created.

Truly, this is a very simple process.

Another way to copy range to another sheet in Excel is using Autofit through VBA. If you want to proceed this way then you will have to follow the given steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, you need to select the Dataset sheet.
• Secondly, press Alt+F11.
• Now the VBA window will appear to you.
• Then insert a Module on that window.

• Lately, you need to paste the below code on that Module.

Code

Sub Copying_Range_Using_AutoFit()
Sheets("Dataset").Select
Range("B3:E12").Copy
Sheets("Using AutoFit").Select
Range("B3").Select
ActiveSheet.Paste
Columns("B:E").AutoFit
End Sub

• Thirdly, save the code and you need to go back to the worksheet.
• Fourthly, click on the View tab.
• Then tap the Macros and View Macros.

• Now select the Macro name as Copying_Range_Using_Autofit.
• Then select Excel VBA Copy Range to Another Sheet.xlsm as Macros in.
• Finally, you need to press Run.

• Eventually, you will see the range copied to another sheet in Excel to the sheet name Using Autofit which I created.

Lastly, we must say that this is a very handy way in Excel.

One of the easiest ways to copy range to another workbook in Excel using VBA has been given below. It is also very simple.

⬇️⬇️ STEPS ⬇️⬇️

• First, go to the Dataset sheet.
• Belatedly, press Alt+F11.
• Now you will find the VBA window.
• Then insert a Module on that window as well.
• Thirdly, you have to write the below code on that Module.

Code

Sub Copying_Range_Toanother_WorkBook()
Workbooks("Excel VBA Copy Range to Another Sheet.xlsm").Worksheets("Dataset").Range("B3:E12"). _
Copy Workbooks("Book1").Worksheets("Another workbook").Range("B3")
End Sub

• Thirdly, save the code and return to the worksheet.
• Fourthly, go to the View tab.
• Now press the Macros and View Macros.

• Now determine the Macro name as Copying_Range_Toanother_Workbook.
• Then select Excel VBA Copy Range to Another Sheet.xlsm as Macros in.
• Then you need to press Run.

• Eventually, you will see the range copied to another sheet in Excel to the sheet name Another workbook of the workbook of Book1 which I created.

Truly, it is a very easy way to Excel VBA copy range.

If you want to copy the range to the last row of another workbook applying VBA in Excel then this portion is for, Hare we will copy the range from the Dataset to the workbook of Book2 at Datasheet. The below picture is the workbook of Book 2.

Follow the below steps here.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, go to the Dataset sheet.
• Secondly, tap Alt+F11.
• Now you will see the VBA window.
• Then insert a Module on that window.
• Thirdly, you have to paste the below code on that Module.

Code

Sub Copying_Range_To_Another_Workbook_Lastcell()
Dim ws1Copy As Worksheet
Dim ws1Destination As Worksheet
Dim lCopy2LastRow As Long
Dim lDest2LastRow As Long
Set ws1Copy = Workbooks("Excel VBA Copy Range to Another Sheet.xlsm").Worksheets("Dataset")
Set ws1Destination = Workbooks("Book2.xlsx").Worksheets("Data")
lCopy2LastRow = ws1Copy.Cells(ws1Copy.Rows.Count, "B").End(xlUp).Row
lDest2LastRow = ws1Destination.Cells(ws1Destination.Rows.Count, "B").End(xlUp).Offset(1).Row
ws1Copy.Range("B6:E" & lCopy2LastRow).Copy ws1Destination.Range("B" & lDest2LastRow)
End Sub

• Thirdly, save the code and return to the worksheet.
• Fourthly, select the View tab.
• Now press the Macros and View Macros.

• Now determine the Macro name as Copying_Range_To_Another_Workbook_Lastcell.
• Then select Excel VBA Copy Range to Another Sheet.xlsm as Macros in.
• Then you need to click on the Run.

• Consequently, you will find the range copied to another sheet in Excel to the sheet name Data of the workbook of Book2 which I created.

Undoubtedly, you can simply do this in Excel VBA. You can also find this by searching- Copy Range to the Last Row of Another Sheet Applying VBA in Excel.

📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️   You need to write the code properly.

🖊️   Don’t forget to save the code on the Module.

🖊️   While working you must give the proper input for Macros in.

📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌  Firstly, we have applied the format in Excel.

📌  Secondly, we used without format procedure using VBA.

📌  Belatedly, we tried to copy range to another sheet in Excel with the format and column width.

📌  Afterward, we used the Autofit procedure.

📌  Fifthly, we tried to show you to copy range to another sheet of another workbook through VBA in Excel.

📌  Finally, we showed you how to copy range to the last row of another workbook using the VBA.

Conclusion

We wish that you would be able to copy range to another sheet applying VBA in Excel using this article. Several methods have been shown here, so you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.