Home » Macros & Excel VBA » How to Use Excel VBA to Copy Range to Another Excel Sheet
Macros & Excel VBA

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

Shamima Sultana Shamima Sultana Feb 16, 2023 2

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

Table of Contents hide
Download Workbook to Practice
8 Suitable Ways to Use Excel VBA Copy Range to Another Sheet
1. Copy a Range to Another Sheet with Formatting
2. Use VBA Code to Copy Range to Another Sheet without Formatting
3. Copy Range with Formatting and Column Width to Another Sheet
4. Utilize VBA Code to Copy Range with Formula to Another Sheet
5. Copy Range with AutoFit to Another Sheet
6. Apply VBA Code to Copy Range to Another Workbook
7. Copy Range to Last Row of Another Sheet
8. Employ VBA Code to Copy Range to Last Row of Another Workbook
Practice Section
Conclusion
Further Readings

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.

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

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

  • Thus, it will Copy the selected range only with Values with no 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

Similar Readings:

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

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

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

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

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.

Further Readings

Tags:

Shamima Sultana

Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in 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.

Related Articles
2 Comments
  1. Reply
    Naveed Jul 29, 2022 at 6:26 PM

    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

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

Affiliate Disclosure:

ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. I earn a small commission if you buy any products using my affiliate links to Amazon.

DMCA.com Protection Status

Join ExcelDemy Forum

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo