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

Here’s an overview of copying a range without formatting.

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

We’ll use a sample dataset that represents personal information, including First Name, Last Name, Full Name, and Email.

Method 1 – Copy a Range to Another Sheet with Formatting

We’ll Copy a range from the Method 1 sheet to the Method 1 (1) sheet.

Open the Developer tab and select Visual Basic. Alternatively, press Alt+ F11 to open the VBA editor.

This will open a new window named Microsoft Visual Basic for Applications.
Select Insert and choose Module to open the module.

Insert 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

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.

Save the code and go back to the worksheet.
Open View and select Macros to open the Macro window.

A dialog box will pop up.
From Macro name, select Copy_Range_to_Another_Sheet_with_Formatting.
Run the selected Macro.

This will copy the selected range with Format to the new sheet we’ve selected.

Method 2 – Use VBA Code to Copy a Range to Another Sheet without Formatting

We’ll copy a range from the Method 2 sheet to the Method 2 (2) sheet.

Open the VBA window by pressing Alt + F11.

Select Insert and choose Module to open the module.

Insert 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

We declared the Sub procedure Copy_Range_to_Another_Sheet_without_Formatting. We’ve taken the range B2:E11 to copy from the existing sheet to the sheet name Method 2 (2). We 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.

Save the code and go back to the worksheet.
Go to the View tab and select Macros.

Select the macro named Copy_Range_to_Another_Sheet_without_Formatting.
Run the selected Macro.

This will copy the selected range and paste Values only with no Formatting.

Method 3 – Copy a Range with Formatting to Another Sheet and Keep the Column Width

We’ll copy a range from the Method 3 sheet to the Method 3 (2) sheet.

Open the VBA editor.

Insert a Module.

Insert 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

We declared the Sub procedure Copy_Range_with_Formatting_and_Column_Width_to_Another_Sheet. We’ve taken the range B2:E11 to copy from the existing sheet to the destination sheet name Method 3 (2). Here, we used the Copy method to copy the selected range. We also used the PasteSpecial method where I mentioned Paste:=xlPasteColumnWidths so that it pastes the selected range along with Format and Column Width.

Save the code and go back to the worksheet.
Select View and go to Macros.

Select the macro named Copy_Range_with_Formatting_and_Column_Width_to_Another_Sheet.
Run the selected Macro.

It will copy the selected range while preserving the Format and the Column Width.

Method 4 – Utilize VBA Code to Copy a Range with Formula to Another Sheet

We’ll copy a range from the Method 4 sheet to the Method 4 (2) sheet.

Open a new VBA window.

Insert a Module.

A Module will open up.
Insert 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

We declared the Sub procedure Copy_Range_with_Formula_to_Another_Sheet. We’ve taken the range B1:E10 to copy from the existing sheet to the destination sheet name WithFormula. We used the Copy method to copy the selected range and used the PasteSpecial method with Paste:=xlPasteFormulas so that it carries the Formulas along with the selected range.

Save the code and go back to the worksheet.
Go to View and select Macros.

Select Copy_Range_with_Formula_to_Another_Sheet and choose This Workbook.
Run the selected Macro.

It will copy all the selected cell ranges with Formulas.

Method 5 – Copy a Range with AutoFit to Another Sheet

We’ll copy a range from the Method 5 sheet to the Method 5 (2) sheet.

Open the VBA editor.

Insert a Module.

Insert 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

We declared the Sub procedure Copy_Range_with_AutoFit_to_Another_Sheet. We selected the worksheet Dataset. The range B2:E11 will be copied from the existing sheet to the destination sheet named Method 5 (2).
We used the Copy method to copy the selected range and the AutoFit method will autofit the given columns B : E.

Save the code and go back to the worksheet.
Open the View tab and select Macros.

Select the Copy_Range_with_AutoFit_to_Another_Sheet and choose This Workbook within Macros in field.
Run the selected Macro.

This will copy the selected range to a new sheet and also AutoFit the columns.

Method 6 – Apply VBA Code to Copy a Range to Another Workbook

We’ll Copy a range from the Method 6 sheet of the existing workbook to Method 6 of the Book 1 workbook.

Press Alt + F11.

Insert a Module.

Insert 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

We declared the Sub procedure Copy_Range_to_Another_Workbook. We’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.
We used the Copy method to copy the selected range to the new workbook.

Save the code and go back to the worksheet.
Open View and select Macros.

Select Copy_Range_to_Another_Workbook.
Run the selected Macro.

It will Copy the selected range from the Dataset sheet to another workbook.

Method 7 – Copy a Range to the Last Row of Another Sheet

We’ll modify the dataset to Full Name, Email, and Address. Here’s the origin Dataset2 sheet.

Here is the Below Last Cell sheet. We’ll Copy a range from the Dataset2 sheet to Method 7 but from the first non-blank cell.

Open the Developer tab and select Visual Basic

Open Insert and select Module.

Insert 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

We declared the Sub procedure Copy_Range_to_Last_Row_of_Another_Sheet. We selected the sheet Dataset2 and then used the Row method to count the Last Row and kept the counted row in lr variable.
We’ll use the range A5:D & lr to copy from the existing sheet to the destination sheet name Method 7.
We used the Row method to count the Last Row of another sheet named Method 7 and kept the counted row in lrAnotherSheet. We used the Copy method to copy the selected range and the AutoFit method will autofit the given columns B : D.

Save the code and go back to the worksheet.
Open View and select Macros.

A dialog box will pop up.
From Macro name, select Copy_Range_to_Last_Row_of_Another_Sheet.
Run the selected Macro.

This will Copy the selected range and Paste it to the last row of another sheet.

Method 8 – Use VBA Code to Copy a Range to Last Row of Another Workbook

We’ll Copy a range from the Dataset2 sheet to Sheet1 of Book2 but from the first non-blank cell.

Open the VBA editor.

Insert a Module.

Insert 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

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

We used Set to set variables for the sheet to copy and the destination sheet.
We used the Row method to find the last row based on the data of column B in the copy range.
We 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.
This will Copy the data of the Dataset2 sheet from the Excel VBA Copy Range to Another Sheet 1.xlsm workbook to the destination Sheet1 of workbook Book1.xlsx.

Save the code and go back to the worksheet.
Open View and select Macros.

Select Copy_Range_to_Last_Row_of_Another_Workbook.
Run the selected Macro.

This will Copy the selected range from the existing sheet to the last row of another workbook.

Practice Section

We’ve provided a practice sheet in the workbook to test these methods.

Download the Practice Workbook

Copy Range to Another Sheet Using VBA.xlsm