VBA Copy Range

What Is VBA Copy Range?

VBA copy range refers to a method by which you can copy a range of cells from a specific location to a target location within different tabs in a worksheet or from one worksheet to another. It is pretty helpful to automate redundancy or build process efficiencies in your application. Microsoft VBA provides various methods by which you can perform the copy range.

Consider the following example for VBA copy range: This code highlighted below copies the formulas in cells A1 to D4 in worksheet Sheet1 and then copies them into destination cells E5 to H8 in Sheet2.

How to use the VBA Copy Range?

To use the VBA Copy Range, follow the instructions outlined below to create your own implementation.

Step 1: Open your data source in Excel. Enable VBA tools in your Excel by navigating File – Options – Customize Ribbon and check the Developer option.

Step 2: Open the VBA Editor by pressing ALT + F11 or navigating to the Developer àVisual Basic option.

Step 3: In the VBA Editor, insert a new module. Do this by right-clicking on Module à Insert à Module.

Step 4: Specify the VBA script to apply the copy range on your Excel data source. You can copy range to an existing sheet, another sheet, or to a new workbook. You can use different methods in the Range object.

Step 5: Give the macro a meaningful name and run it by pressing F5 or clicking on the Run icon from the icon toolbar.

The VBA script will then perform the task according to the specified code, and you will be able to see the changes in the sheet.

Examples

In this section, we will use different examples to demonstrate VBA copy range syntax and create functions to help you implement it in real-world scenarios.

Example #1 – Copy Range to Existing Sheet Using VBA

In this example, we will show you how to copy the range to an existing sheet using VBA. For this purpose, we have created a sample Excel file with data science salary information highlighting the salary details across different experience levels and job titles in 2024. We have applied different color coding to show different experience levels and their salaries in the Excel file.

To copy the range to the existing sheet using VBA, follow the instructions outlined below:

Step 1: Create a new Excel worksheet with the data science salary information with formatting applied as shown below.

Step 2: If you are using VBA for the 1^st time, you need to enable it in your Excel worksheet. You can do so by navigating File à Options.

Step 3: In the Excel Options screen, click on Customize Ribbon. Under the Customize the Ribbon section, click on the Developer check box. Click on OK to save the changes.

Step 4: Navigate to the Developer tab and click on the Visual Basic option.

It will open the Microsoft Visual Basic for Applications editor screen, as shown below.

Step 5: In the VBA editor, right-click on the Modules under VBAProject. Select Insert -Module.

It will open a new blank VBA editor screen, as seen below.

Step 6: Specify the logic for copying the range in the worksheet from one range to another within the same sheet. Here, we are copying the data in columns A—K to M—W in the same sheet.

You can copy the code provided below

Sub CopyRange_Existing_Sheet()

‘Copy the range A-K in current sheet to M-W

Range(“A:K”).Copy Range(“M:W”)

End Sub

Notice that the text highlighted in green is a comment highlighting the expected behavior of the Range copy method.

Step 7: Give the macro a meaningful name. Here, we have named the macro CopyRange_Existing_Sheet.

Step 8: Click on the Run Macro icon in the icon toolbar as shown below. Alternatively, you can run the macro by pressing the F5 button.

You will notice that the macro has copied the data from A: K to M: W in the DataTab tab in the Excel worksheet, following the original formatting, as shown below.

Example #2 – Copy Range to Another Sheet Using VBA

In this example, we will demonstrate how to copy a range to another sheet using VBA using the data science sample dataset. This is the same dataset we used in example 1. We have defined a macro to copy the range in this worksheet to a new worksheet.

To copy the range to another sheet using VBA, follow the step-by-step instructions outlined below:

Step 1: Open the Excel worksheet created in Example 1. The color-coded dataset is in the DataTab, as shown below.

Step 2: Open the VBAProject window. Right-click on the Modules under VBAProject and choose Insert à Module.

It will open a new blank VBA editor screen, as shown below. Rename the module to copyRange_New_Sheet.

Step 3: Click on the Run icon in the icon toolbar. It will open up a Macros window. Specify the Macro Name and click on Create to create a macro.

Step 4: In the macro window, specify the logic to copy the range to another sheet. Here, we are copying the range A: K  in the current worksheet to a new worksheet.

You can specify the code provided below

Sub CopyRange_New_Sheet()

‘Copy to a new sheet

ActivateSheet.Range(“A:K”).Select

  Selection.Copy

  Sheets.Add After!=ActiveSheet

  ActiveSheet.Paste

End Sub

Step 5: Run the VBA macro created above. It will create multiple new Sheet/s depending on how many times you execute the macro, i.e.,  Sheet2 and Sheet3.

You can see that the two new tabs, Sheet2 and Sheet3, were created after executing the macro in Excel.

Example #3 – Copy to Existing Workbook Using VBA

In this example, we will demonstrate how to copy to an open workbook using VBA. We have used the Vehicle Sales dataset, which contains the different vehicle types, sales volume, and total number of vehicles sold, to demonstrate this.

To copy to an open existing workbook, you may refer to the step-by-step guide provided below:

Step 1: Open the existing Excel workbook from which you want to copy the data, i.e., the source workbook.

Step 2: Open the new workbook where you want to copy the data. It is the target workbook.

Step 3: In the source workbook, navigate to the VBA screen. Right-click on the Modules and choose Insert à Module.

It will open a new macro window, as shown below. Click on the Sub Sub/UserForm run icon.

Step 4: Specify the Macro name in the Macros window. Click on the Create button.

Step 5: Specify the logic to copy data from an existing workbook and paste it to an open existing workbook, i.e., NewDataSheet.xlsx. Click on the Run icon in the toolbar.

Apply the below code changes to your workbook. The below code selects the data in the data range A to K and copies to the clipboard. Then it opens the file in the local path and then pastes the data into a new sheet.

Sub copy_to_existing_workbook()

‘copy to an existing workbook that’s open

Range(“A:K”).Select

   Selection.copy

   Workbooks.Open Filename:=<local filepath>

   Sheets.Add After!=ActiveSheet

   ActiveSheet.Paste

End Sub

You can see the data in the source workbook copied and pasted into the open workbook.

Example #4 – Copy to Another Workbook Using VBA

In this example, we will see how to copy data to another workbook using VBA. We have created a new worksheet, NewDataSheet, containing the Vehicle sales volume and sales turnover information. We will demonstrate how to copy a set of data from an existing workbook to an entirely new workbook using the VBA script.

To copy to another workbook using VBA, follow the step-by-step guide below:

Step 1: Create or source Vehicle sales data into an Excel worksheet.

Step 2: Open the VBA window. Right-click on Module and then choose Insert – Module.

Step 3: Click on the Run icon in the icon toolbar. It will open the Macros window. Provide a Macro Name, e.g., copy_to_new_workbook, and then click on the Create button.

Step 4: In the macro, specify the logic for copying the data from an existing workbook and pasting it into a new workbook.

The code specified below will copy the data in column A:K in the existing workbook.

Open the new workbook NewDataSheet.xslx and Paste the data to the new workbook.

Apply the below code changes to your workbook. The below code selects the data in the data range A to K and copies to the clipboard. Then it opens the file in the local path and then pastes the data into a new sheet.

Sub copy_to_new_workbook()

‘Copy to new workbook

  Range(“A:K”).Select

  Selection.copy

  Workbooks.Open Filename:=<local filename>

  Sheets.Add After:=ActiveSheet

  ActiveSheet.Paste

End Sub

Step 5: Save the macro to a meaningful macro name and then click on Run.

As you can see, the macro copied the data from an existing Excel workbook and pasted it into a new workbook with the same formatting as shown below.

Important Things to Note

1. You can use the Range object methods such as xlPasteValues, xlPasteAll, xlPasteSpecialOperationAdd, and PasteSpecial to paste values with or without formatting.
2. VBA Copy range supports conditional-based copying of the rows of data into a new sheet.
3. Always validate the destination range before you execute your macro, as there is a chance it may override your current data.
4. Be cautious if you are using a relative sheet name, such as ActiveSheet. It is recommended that you use a specific sheet to avoid any confusion.
5. Ensure you have accurately specified the data range to avoid any runtime error while executing the macro run.
6. Always test your macro script thoroughly to ensure that the code behavior is as per your expectations.

Frequently Asked Questions (FAQs)

Recommende Articles

Guide to What Is VBA Copy Range. Guide to What Is VBA Copy Range. We learn how to use it to copy a range to different destinations in worksheets with step-wise examples. You can learn more from the following articles.

VBA Randomize

VBA Hyperlinks

VBA IsNumber