Whenever you need you can copy a range from one sheet to another sheet or workbook. There are different types of copy and pasting. Following this article, you will learn different ways to use Excel VBA copy range to another sheet.
Download Workbook to Practice
8 Suitable Ways to Use Excel VBA Copy Range to Another Sheet
To make the explanation lively, I’m going to use a sample dataset that represents the personal information of particular persons. The dataset has 4 columns. These columns are the First Name, Last Name, Full Name, and Email.
1. Copy a Range to Another Sheet with Formatting
Whenever you want to Copy a range from one sheet to another sheet with Format you can simply do it by using the VBA. Here, we’ll Copy a range from the Method 1 sheet to the Method 1 (1) sheet. So, let’s start the procedure,
- Firstly, open the Developer tab >> select Visual Basic.
- Alternatively, you also can use the ALT + F11 keyboard to open the VBA editor.
- Next, it will open a new window named Microsoft Visual Basic for Applications.
- From the window, select Insert >> Module to open the module.
- Instantly, the Module will open up.
- Then, type the following code in the opened Module.
Sub Copy_Range_to_Another_Sheet_with_Formatting()
Range("B2:E12").Copy Worksheets("Method 1 (2)").Range("B2:E12")
End Sub
- Finally, Save the code and go back to the worksheet.
- Then, open the View >> Macros >>View Macros to open the Macro window.
- Instantly, a dialog box will pop up.
- Now, from the Macro name select the Copy_Range_to_Another_Sheet_with_Formatting.
- Finally, Run the selected Macro.
- As a result, it will Copy the selected range with Format to the new sheet I’ve selected Method 1 (2).
2. Use VBA Code to Copy Range to Another Sheet without Formatting
It is also possible to Copy a range to another sheet without Format by using the VBA. Here, I’ll Copy a range from the Method 2 sheet to the Method 2 (2) sheet. Let’s begin the procedure.
- Now, open the Developer >> Visual Basic to open the Macro window.
- Alternatively, use the ALT + F11.
- Instantly, it will open Microsoft Visual Basic for Applications.
- After that, select Insert >> Module to open the module.
- As a result, a Module will open then type the following code in the opened Module.
Sub Copy_Range_to_Another_Sheet_without_Formatting()
Range("B2:E11").Copy
Sheets("Method 2 (2)").Range("B2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
- Finally, Save the code and go back to the worksheet.
- Next, open the View tab >>Â Macros >> View Macros.
- Instantly, a dialog box will pop up.
- Now, from the Macro name select the Copy_Range_to_Another_Sheet_without_Formatting.
- Finally, Run the selected Macro.
- Thus, it will Copy the selected range only with Values with no Formatting.
3. Copy Range with Formatting and Column Width to Another Sheet
Sometimes you may want to Copy any selected range as it is, for you can Copy a range with Format and Column Width. Here, I’ll Copy a range from the Method 3 sheet to the Method 3 (2) sheet. To see the procedure to do the task using VBA,
- First, select Developer >> Visual Basic.
- Alternatively, you also can use the ALT + F11 keyboard to open the VBA editor.
- As a result, it will open a new window of Microsoft Visual Basic for Applications.
- From this window, select Insert >> Module.
- Now, a Module will open and type the following code in the opened Module.
Sub Copy_Range_with_Formatting_and_Column_Width_to_Another_Sheet()
Range("B2:E11").Copy Destination:=Sheets("Method 3 (2)").Range("B2")
Range("B2:E11").Copy
Sheets("Method 3 (2)").Range("B2").PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
- Finally, Save the code and go back to the worksheet.
- Next, select View >> Macros >> View Macros to view the saved macros.
- Instantly, a dialog box will pop up.
- Now, from the Macro name select the Copy_Range_with_Formatting_and_Column_Width_to_Another_Sheet.
- Finally, Run the selected Macro.
- As a result, it will Copy the selected range with Format and Column Width.
4. Utilize VBA Code to Copy Range with Formula to Another Sheet
In case you have any formula in your dataset you want to copy. No worries! You can copy the range containing Formula to another sheet keeping the formula intact. Here, I’ll Copy a range from the Method 4 sheet to the Method 4 (2) sheet. Let’s begin the procedure.
- To begin with, select Developer>> Visual Basic.
- Alternatively, you also can use the ALT + F11 keyboard.
- So, it will open Microsoft Visual Basic for Applications window.
- From there, select Insert >> Module.
- As a result, a Module will open up.
- Then, type the following code in the opened Module.
Sub Copy_Range_with_Formula_to_Another_Sheet()
Range("B2:E11").Copy
Sheets("Method 4 (2)").Range("B2").PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
- Finally, Save the code and go back to the worksheet.
- Then, open View >>Â Macros >> View Macros.
- Instantly, a dialog box will pop up.
- Now, from the Macro name select the Copy_Range_with_Formula_to_Another_Sheet also select the workbook This Workbook.
- Finally, Run the selected Macro.
- Thus, it will Copy all the selected cell ranges with Formulas.
Similar Readings:
- How to Use VBA Range Offset (11 Ways)
- VBA for Each Cell in Range in Excel (3 Methods)
- How to Use the Range Object of VBA in Excel (5 Properties)
5. Copy Range with AutoFit to Another Sheet
While Copying a range to another sheet you also can use the AutoFit method in the VBA to AutoFit the copied range in the new sheet. Here, I’ll Copy a range from the Method 5 sheet to the Method 5 (2) sheet. Let’s start the procedure.
- Firstly, open Developer >> Visual Basic.
- Alternatively, you also can use the ALT + F11 keyboard to open the VBA editor.
- Therefore, it will open a window of Microsoft Visual Basic for Applications.
- Then, select Insert >> Module to open the module.
- As a result, a Module will open up.
- Then, type the following code in the opened Module.
Sub Copy_Range_with_AutoFit_to_Another_Sheet()
Sheets("Method 5").Select
Range("B2:E11").Copy
Sheets("Method 5 (2)").Select
Range("B2").Select
ActiveSheet.Paste
Columns("B:E").AutoFit
End Sub
Here, I used the Copy method to copy the selected range and the AutoFit method will autofit the given columns B : E.
- Finally, Save the code and go back to the worksheet.
- Next, open the View tab >> from Macros >> select View Macros
- Instantly, a dialog box will pop up.
- Now, from the Macro name select the Copy_Range_with_AutoFit_to_Another_Sheet also select This Workbook within Macros in field.
- Finally, Run the selected Macro.
- Hence, it will Copy the selected range to a new sheet and also AutoFit the columns.
6. Apply VBA Code to Copy Range to Another Workbook
If you want you can also Copy a range from one sheet to another sheet of a different workbook. Here, I’ll Copy a range from the Method 6 sheet of the existing workbook to Method 6 of the Book 1 workbook. Let’s start the procedure.
- Firstly, open the Developer tab >> select Visual Basic (you also can use the ALT + F11 keyboard).
- So, it will open Microsoft Visual Basic for Applications window.
- Next, select Insert >> Module.
- Instantly, a Module will open up.
- Then, type the following code in the opened Module.
Sub Copy_Range_to_Another_Workbook()
Workbooks("Excel-VBA-Copy-Range-to-Another-Sheet (1).xlsm").Worksheets("Method 6").Range("B2:E11"). _
Copy Workbooks("Book1").Worksheets("Method 6").Range("B2")
End Sub
Here, I used the Copy method to copy the selected range to the new workbook.
- Finally, Save the code and go back to the worksheet.
- Next, open View >> Macros >> View Macros to view the Macro.
- Instantly, you can see a dialog box.
- Now, from the Macro name select the Copy_Range_to_Another_Workbook.
- Finally, Run the selected Macro.
- Now, it will Copy the selected range from the Dataset sheet to another workbook.
7. Copy Range to Last Row of Another Sheet
In any case, if you want to Copy a range to another sheet from a particular cell or the last cell you can do it using the VBA. Before entering into the procedure, I want to tell you that, I’ve taken a new sheet consisting of Full Name, Email, and Address. Let’s observe the Dataset2 sheet first.
Here is the Below Last Cell sheet.
Here, I’ll Copy a range from the Dataset2 sheet to Method 7 but from the first non-blank cell.
- To begin with, open the Developer tab >> select Visual Basic
- Next, it will open Microsoft Visual Basic for Applications.
- Then, open Insert >> select Module.
A Module will open then type the following code in the opened Module.
Sub Copy_Range_to_Last_Row_of_Another_Sheet()
Sheets("Dataset2").Select
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Range("A5:D" & lr).Copy
Sheets("Method 7").Select
lrAnotherSheet = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Cells(lrAnotherSheet + 1, 1).Select
ActiveSheet.Paste
Columns("B:D").AutoFit
End Sub
Then, I have taken the range A5:D & lr to copy from the existing sheet to the destination sheet name Method 7.
Again, used the Row method to count the Last Row of another sheet named Method 7 and kept the counted row in lrAnotherSheet. Here, I used the Copy method to copy the selected range and the AutoFit method will autofit the given columns B : D.
- Finally, Save the code and go back to the worksheet.
- Next, open View >> Macros >> View Macros to watch saved macros.
- Instantly, a dialog box will pop up.
- Now, from the Macro name select the Copy_Range_to_Last_Row_of_Another_Sheet.
- Finally, Run the selected Macro.
- Hence, it will Copy the selected range and will Paste it from the last row of another sheet.
8. Employ VBA Code to Copy Range to Last Row of Another Workbook
If you want to Copy a range to the last row of another workbook sheet you also can use the VBA. Here, I’ll Copy a range from the Dataset2 sheet to Sheet1 of Book2 but from the first non-blank cell.
Firstly, open Developer >> Visual Basic to open the Macro.
- Here, you also can use the ALT + F11 keyboard to open the VBA editor.
- Next, it will open a new window of Microsoft Visual Basic for Applications.
- From there, select Insert >> Module.
- So, a Module will open and type the following code in the opened Module.
Sub Copy_Range_to_Last_Row_of_Another_Workbook()
Dim wsCopy As Worksheet
Dim wsDestination As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Set wsCopy = Workbooks("Excel-VBA-Copy-Range-to-Another-Sheet (1).xlsm").Worksheets("Dataset2")
Set wsDestination = Workbooks("Book1.xlsx").Worksheets("Method 8")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
lDestLastRow = wsDestination.Cells(wsDestination.Rows.Count, "B").End(xlUp).Offset(1).Row
wsCopy.Range("B5:D" & lCopyLastRow).Copy wsDestination.Range("B" & lDestLastRow)
End Sub
Here, I declared the Sub procedure Copy_Range_to_Last_Row_of_Another_Workbook where wsCopy and wsDestination are Worksheet type, lCopyLastRow and lDestLastRow are Long type.
First, used Set to set variables for the sheet to copy and the destination sheet.
Next, used the Row method to find the last row based on the data of column B in the copy range.
Again, used the Row method to find the first blank row based on data of column B in the destination range and also used Offset to move down one property.
Finally, Copied the data of Dataset2 sheet from the Excel VBA Copy Range to Another Sheet 1.xlsm workbook to the destination Sheet1 of workbook Book1.xlsx.
- Now, Save the code and go back to the worksheet.
- Next, open View >> Macros >> View Macros
- A dialog box will pop up.
- Now, from the Macro name select the Copy_Range_to_Last_Row_of_Another_Workbook.
- Finally, Run the selected Macro.
- Hence, it will Copy the selected range from the existing sheet to the last row of another workbook.
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained ways of Excel VBA copy range to another sheet.
Conclusion
In this article, I’ve explained 8 different types of easy and quick ways to use Excel VBA to copy range to another sheet. These different ways will help you to copy a range from one sheet to another and also from one sheet to another workbook. To read similar articles, check out the ExcelDemy website. Last but not least, if you have any kind of suggestions, ideas, and feedback please feel free to comment down below.
i want to make ledger of my vendors and need your help. I will send u the file of xl on your reply
Hello Naveed,
Hope you are doing well. Kindly send me the details of your problem including the Excel file via my Gmail account [email protected]
Thank you