How to Use Excel VBA to Copy Range to Another Excel Sheet

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel VBA Copy Range to Another Sheet


How to Use Excel VBA Copy Range to Another Sheet: 8 Suitable Ways

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.

Dataset for Using Excel VBA to Copy Range to Another Sheet


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.

Inserting Macro to Use VBA to Copy Range to Another Sheet with Formatting

  • Next, it will open a new window named Microsoft Visual Basic for Applications.
  • From the window, select Insert >> Module to open the module.

Inserting Module to Write VBA Code

  • 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

Writing VBA Code to Copy Range to Another Sheet with Formatting

Here, we have declared the Sub procedure Copy_Range_to_Another_Sheet_with_Formatting(). We have taken range B2:E12 to copy from the existing sheet to the sheet name Method 1 (2). Here, we have used the Copy method to copy the selected range, Copy method copies any range with Format.
  • Finally, Save the code and go back to the worksheet.
  • Then, open the View >> Macros >>View Macros to open the Macro window.

Opening Macro to Run VBA Code

  • 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.

Running Macro to Copy Range to Another Sheet with Formatting

  • As a result, it will Copy the selected range with Format to the new sheet I’ve selected Method 1 (2).

Result After Using Excel VBA to Copy Range to Another Sheet with Formatting

Read More: Macro to Copy and Paste from One Worksheet to Another


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.

Opening VBA Editor Code to Copy Range to Another Sheet without Formatting

  • Instantly, it will open Microsoft Visual Basic for Applications.
  • After that, select Insert >> Module to open the module.

Inserting Module in Visual Basic Editor

  • 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

Writing VBA Code to Copy Range to Another Sheet without Formatting

Here, I declared the Sub procedure Copy_Range_to_Another_Sheet_without_Formatting. I’ve taken range B2:E11 to copy from the existing sheet to the sheet name Method 2 (2). Here, I used the Copy method to copy the selected range but also mentioned Paste:=xlPasteValues in the PasteSpecial method so that it will only paste Values of the selected range, not the format.
  • Finally, Save the code and go back to the worksheet.
  • Next, open the View tab >>  Macros >> View Macros.

Opening Macro to Run Inserted VBA Code

  • 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.

Running VBA Code to Copy Range to Another Sheet without Formatting

Result after using Excel VBA to Copy Range to Another Sheet without 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.

Opening Visual Basic Editor

  • As a result, it will open a new window of Microsoft Visual Basic for Applications.
  • From this window, select Insert >> Module.

Inserting Module to Write VBA Code to Copy Range to Another Sheet

  • 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

Writing VBA Code to Copy Range with Formatting and Column Width to Another Sheet

Here, I declared the Sub procedure Copy_Range_with_Formatting_and_Column_Width_to_Another_Sheet. I’ve taken range B2:E11 to copy from the existing sheet to the destination sheet name Method 3 (2). Here, I used the Copy method to copy the selected range. I also used the PasteSpecial method where I mentioned Paste:=xlPasteColumnWidths so that it pastes the selected range along with Format and Column Width.
  • Finally, Save the code and go back to the worksheet.
  • Next, select View >> Macros >> View Macros to view the saved macros.

Opening Macro Dialog Box

  • 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.

Running Macro to Copy Range with Formatting and Column Width to Another Sheet

  • As a result, it will Copy the selected range with Format and Column Width.

Result After Using Excel VBA to Copy Range with Formatting and Column Width to Another Sheet


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.

Inserting Macro to Utilize VBA Code to Copy Range with Formula to Another Sheet

  • So, it will open Microsoft Visual Basic for Applications window.
  • From there, select Insert >> Module.

Inserting 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

Writing VBA Code in Module

Here, I declared the Sub procedure Copy_Range_with_Formula_to_Another_Sheet. I’ve taken range B1:E10 to copy from the existing sheet to the destination sheet name WithFormula. Here, I used the Copy method to copy the selected range. I also used the PasteSpecial method where I mentioned Paste:=xlPasteFormulas so that it carries the Formulas along with the selected range.
  • Finally, Save the code and go back to the worksheet.
  • Then, open View >>  Macros >> View Macros.

Opening Macro to Run Inserted VBA Code

  • 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.

Running Macro to Copy Range with Formula to Another Sheet

  • Thus, it will Copy all the selected cell ranges with Formulas.

Result after Utilizing Excel VBA Code to Copy Range with Formula to Another Sheet


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.

Inserting Macro to Copy Range with AutoFit to Another Sheet

  • Therefore, it will open a window of Microsoft Visual Basic for Applications.
  • Then, select Insert >> Module to open the module.

Opening Module to Write VBA Code

  • 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

Writing VBA Code

Here, I declared the Sub procedure Copy_Range_with_AutoFit_to_Another_Sheet. First, I selected the worksheet Dataset. Then, take the range B2:E11 to copy from the existing sheet to the destination sheet named Method 5 (2).
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

Using View Macros Feature

  • 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.

Running Macro to Copy Range with AutoFit to Another Sheet

  • Hence, it will Copy the selected range to a new sheet and also AutoFit the columns.

Result After Using Excel VBA to Copy Range with AutoFit to Another Sheet

Read More: Excel VBA to Copy Rows to Another Worksheet Based on Criteria


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).

Opening VBA Editor to Copy Range to Another Workbook

  • So, it will open Microsoft Visual Basic for Applications window.
  • Next, select Insert >> Module.

Inserting Module to Write VBA Code

  • 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

Writing VBA Code in Module

Here, I declared the Sub procedure Copy_Range_to_Another_Workbook. I’ve taken the range B2:E11 from sheet name Method 6 to copy from the existing sheet to the new workbook name Book1 and sheet name Method 6.
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.

Opening Macro Dialog Box to Run Inserted VBA Code

  • Instantly, you can see a dialog box.
  • Now, from the Macro name select the Copy_Range_to_Another_Workbook.
  • Finally, Run the selected Macro.

Running Macro to Copy Range to Another Workbook

  • Now, it will Copy the selected range from the Dataset sheet to another workbook.

Result after Utilizing Excel VBA to Copy Range to Another Workbook

Read More: Excel VBA: Copy Range 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.

Second Dataset to Use

Here is the Below Last Cell sheet.

Showing Last Row of Sheet Where Dataset 2 Be Put 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

Inserting Macro to Copy Range of Another Sheet

  • Next, it will open Microsoft Visual Basic for Applications.
  • Then, open Insert >> select Module.

Inserting Module in VBA Editor

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

Writing VBA Code to Copy Range to Last Row of Another Sheet

Here, I declared the Sub procedure Copy_Range_to_Last_Row_of_Another_Sheet. Firstly, I selected the sheet Dataset2 and then used the Row method to count the Last Row and kept the counted row in lr variable.
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.

Opening Macro to Run Inserted VBA Code

  • 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.

Running Macro to Copy Range to Last Row of Another Sheet

  • Hence, it will Copy the selected range and will Paste it from the last row of another sheet.

Result After Using Excel VBA to Copy Range to 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.

  • Showing Another WorkbookFirstly, open Developer >> Visual Basic to open the Macro.
  • Here, you also can use the ALT + F11 keyboard to open the VBA editor.

Inserting Macro to Copy Range of Another Workbook

  • Next, it will open a new window of Microsoft Visual Basic for Applications.
  • From there, select Insert >> Module.

Inserting 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

Writing VBA Code to Copy Range of Another Workbook

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

Opening Macro to Run Inserted VBA Code

  • 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.

Running Macro to Copy Range to Last Row of Another Workbook

  • Hence, it will Copy the selected range from the existing sheet to the last row of another workbook.

Result After Using Excel VBA to Copy Range of Another Workbook

Read More: How to Open Another Workbook and Copy Data with Excel VBA


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained ways of Excel VBA copy range to another sheet.

Practice Section for Using Excel VBA to Copy Range to Another Sheet


Download Workbook to Practice


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. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment below.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

2 Comments
  1. i want to make ledger of my vendors and need your help. I will send u the file of xl on your reply

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo