Excel VBA Copy Range to Another Sheet (8 Easiest Ways)

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 get to know different 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.

Sample dataset to Use VBA Copy a Range to another Sheet


Download Workbook to Practice


8 Ways to Excel VBA Copy Range to Another Sheet

1. Copy a Range to Another Sheet with Format

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, I’ll Copy a range from Dataset sheet to WithFormat sheet.

Let’s start the procedure,
First, open the Developer tab >> select Visual Basic

VBA Copy a Range to Another Sheet with Format

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 there, open Insert >> select Module.

A Module will open then type the following code in the opened Module.

Sub Copy_Range_withFormat_ToAnother_Sheet()
Range("B1:E10").Copy Worksheets("WithFormat").Range("B1:E10")
End Sub

VBA Copy a Range to Another Sheet with Format

Here, I declared the Sub procedure Copy_Range_withFormat_ToAnother_Sheet
I’ve taken range B1:E10 to copy from the existing sheet to the sheet name WithFormat.
Here, I 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.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

VBA Copy a Range to Another Sheet with Format

Now, from the Macro name select the Copy_Range_withFormat_ToAnother_Sheet also select the workbook within Macros in.
Finally, Run the selected Macro.

Hence, it will Copy the selected range with Format to the new sheet I’ve selected (WithFormat).


2. VBA Copy a Range to Another Sheet without Format

It is also possible to Copy a range to another sheet without Format by using the VBA.

Here, I’ll Copy a range from Dataset sheet to WithoutFormat sheet.

Let’s begin the procedure,
Now, open the Developer tab >> select Visual Basic (use the ALT + F11)

VBA Copy a Range to Another Sheet without Format

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_WithoutFormat_Toanother_Sheet()
Range("B1:E10").Copy
Sheets("WithoutFormat").Range("B1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

VBA Copy a Range to Another Sheet without Format

Here, I declared the Sub procedure Copy_Range_WithoutFormat_Toanother_Sheet
I’ve taken range B1:E10 to copy from the existing sheet to the sheet name WithoutFormat.
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 >> from Macros >> select View Macros

➤ A dialog box will pop up.

VBA Copy a Range to Another Sheet without Format

Now, from the Macro name select the Copy_Range_WithoutFormat_Toanother_Sheet and also select the workbook within Macros in.
Finally, Run the selected Macro.

Thus, it will Copy the selected range only with Values no Format.


3. Copy a Range to Another Sheet with Format and Column Width

Sometimes you may want to Copy any selected range as it is, for that you can Copy a range with Format and Column Width.

Here, I’ll Copy a range from Dataset sheet to Format & Column Width sheet.

To see the procedure to do the task using VBA,
First, open the Developer tab >> select Visual Basic
You also can use the ALT + F11 keyboard to open the VBA editor.

VBA Copy a Range to Another Sheet with Format and Column Width

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

A Module will open then type the following code in the opened Module.

Sub Copy_Range_to_Another_Sheet_with_FormatAndColumnWidth()
Range("B1:E10").Copy Destination:=Sheets("Format & ColumnWidth").Range("B1")
Range("B1:E10").Copy
Sheets("Format & ColumnWidth").Range("B1").PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

VBA Copy a Range to Another Sheet with Format and Column Width

Here, I declared the Sub procedure Copy_Range_to_Another_Sheet_with_FormatAndColumnWidth
I’ve taken range B1:E10 to copy from the existing sheet to the destination sheet name Format & Column Width.
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, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

VBA Copy a Range to Another Sheet with Format and Column Width

Now, from the Macro name select the Copy_Range_to_Another_Sheet_with_FormatAndColumnWidth and also select the workbook within Macros in.
Finally, Run the selected Macro.

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


4. VBA Copy a Range to Another Sheet with Formula

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 Dataset sheet to WithFormula sheet

Let’s begin the procedure,
To begin with, open the Developer tab >> select Visual Basic (you also can use the ALT + F11 keyboard)

VBA Copy a Range to Another Sheet with Formula

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

A Module will open then type the following code in the opened Module.

Sub Copy_Range_withFormula_ToAnother_Sheet()
Range("B1:E10").Copy
Sheets("WithFormula").Range("B1").PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

VBA Copy a Range to Another Sheet with Formula

Here, I declared the Sub procedure Copy_Range_withFormula_ToAnother_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 the View tab >>  from Macros >> select View Macros

➤ A dialog box will pop up.

VBA Copy a Range to Another Sheet with Formula

Now, from the Macro name select the Copy_Range_withFormula_ToAnother_Sheet also select the workbook within Macros in.
Finally, Run the selected Macro.

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


Similar Readings:


5. Copy a 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 Dataset sheet to AutoFit sheet

Let’s start the procedure,
First, open the Developer tab >> select Visual Basic
You also can use the ALT + F11 keyboard to open the VBA editor.

VBA Copy a Range with AutoFit to Another Sheet

Next, it will open a window of 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_withFormat_AutoFit()
Sheets("Dataset").Select
Range("B1:E10").Copy
Sheets("AutoFit").Select
Range("B1").Select
ActiveSheet.Paste
Columns("B:E").AutoFit
End Sub

VBA Copy a Range with AutoFit to Another Sheet

Here, I declared the Sub procedure Copy_Range_withFormat_AutoFit
First, I selected the worksheet Dataset. Then taken the range B1:E10 to copy from the existing sheet to the destination sheet named AutoFit.
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

➤ A dialog box will pop up.

VBA Copy a Range with AutoFit to Another Sheet

Now, from the Macro name select the Copy_Range_withFormat_AutoFit also select the workbook within Macros in.
Finally, Run the selected Macro.

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


6. VBA Copy a 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 Dataset sheet to Sheet1 of Book1 workbook.

Let’s start the procedure,
First, open the Developer tab >> select Visual Basic (you also can use the ALT + F11 keyboard)

VBA Copy a Range to Another Workbook

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

A Module will open then type the following code in the opened Module.

Sub Copy_Range_WithFormat_Toanother_WorkBook()
Workbooks("Excel VBA Copy Range to Another Sheet.xlsm").Worksheets("Dataset").Range("B3:E10"). _
Copy Workbooks("Book1").Worksheets("Sheet1").Range("B3")
End Sub

VBA Copy a Range to Another Workbook

Here, I declared the Sub procedure Copy_Range_WithFormat_Toanother_WorkBook
I’ve taken the range B3:E10 from sheet name Dataset to copy from the existing sheet to the new workbook name Book1 and sheet name Sheet1.
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 the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

VBA Copy a Range to Another Workbook

Now, from the Macro name select the Copy_Range_WithFormat_Toanother_WorkBook also select the workbook within Macros in.
Finally, Run the selected Macro.

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


7. Copy a Range to the 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 two new sheets consisting Full Name, Email, and Address.
Let’s observe the Dataset2 sheet first.

VBA Copy a Range to the Last Row of Another Sheet

Here is the Below Last Cell sheet.

Here, I’ll Copy a range from Dataset2 sheet to Below Last Cell but from the first non-blank cell.

To begin with, open the Developer tab >> select Visual Basic

VBA Copy a Range to the Last Row of Another Sheet

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_BelowLastCell_AnotherSheets()
Sheets("Dataset2").Select
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Range("A2:C" & lr).Copy
Sheets("Below Last Cell").Select
lrAnotherSheet = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Cells(lrAnotherSheet + 1, 1).Select
ActiveSheet.Paste
Columns("A:C").AutoFit
End Sub

VBA Copy a Range to the Last Row of Another Sheet

Here, I declared the Sub procedure Copy_Range_BelowLastCell_AnotherSheets
First, I selected the sheet Dataset2 and then used the Row method to count the Last Row and kept the counted row in lr.
Then taken the range A2:C & lr to copy from the existing sheet to the destination sheet name Below Last Cell.
Again, used the Row method to count the Last Row of another sheet named Below Last Cell 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 A:C.

Finally, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

VBA Copy a Range to the Last Row of Another Sheet

Now, from the Macro name select the Copy_Range_BelowLastCell_AnotherSheets also select the workbook within Macros in.
Finally, Run the selected Macro.

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


8. VBA Copy a Range to the 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 Dataset2 sheet to Sheet1 of Book2 but from the first non-blank cell.

To start the procedure,
First, open the Developer tab >> select Visual Basic
You also can use the ALT + F11 keyboard to open the VBA editor.

VBA Copy a Range to the Last Row of Another Workbook

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

A Module will open then type the following code in the opened Module.

Sub Copy_Range_BelowLastCell_To_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.xlsm").Worksheets("Dataset2")
Set wsDestination = Workbooks("Book2.xlsx").Worksheets("Sheet1")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
lDestLastRow = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Offset(1).Row
wsCopy.Range("A2:C" & lCopyLastRow).Copy wsDestination.Range("A" & lDestLastRow)
End Sub

VBA Copy a Range to the Last Row of Another Workbook

Here, I declared the Sub procedure Copy_Range_BelowLastCell_To_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 data of column A in the copy range.
Again, used the Row method to find the first blank row based on data of column A in the destination range also used Offset to move down one property.
Finally, Copied the data of Dataset2 sheet from the Excel VBA Copy Range to Another Sheet.xlsm workbook to the destination Sheet1 of workbook Book2.xlsx.

Now, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

VBA Copy a Range to the Last Row of Another Workbook

Now, from the Macro name select the Copy_Range_BelowLastCell_To_Another_Workbook also select the workbook within Macros in.
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 of Excel VBA 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, and feedback please feel free to comment down below.


Further Readings

Tags:

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

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

ExcelDemy
Logo