Excel VBA: Cell Reference in Another Sheet (4 Methods)

Following the formation of the macro and declaration of the variables, the very next step is to set up VBA cell references that refer to each variable and can then be used to change the data inside the Spreadsheet. To do so, you must be familiar with the many ways available in VBA. In this article, we will show you how to apply cell reference in another sheet using Excel VBA.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

4 Effective Ways to Perform Cell Reference in Another Sheet with Excel VBA

In the sections below we will discuss 4 ways to do a cell reference. We will apply cell references in another sheet for a single cell, for a range, for multiple ranges. Additionally, in the last method, we will refer cells for different workbooks also for different worksheets. In the image below, a sample data set is provided to apply the referencing.

1. Apply a Reference in Another Sheet for a Cell with Excel VBA

Let’s say, our current worksheet is ‘Sheet2’, but we want to copy the data in cell D5 from ‘Sheet1’. Simply, follow the steps below to do the referencing.

Step 1:

• First of all, press  Alt + F11  to open the VBA Macro.
• Click on the Insert.
• Choose the Module.

Step 2:

• Paste the following VBA.

Sub Select_a_Cell()
    Worksheets("sheet1").Range("D5").Copy
End Sub

Step 3:

• Save the program and press  F5  to run it.
• Therefore, you will see, that cell  D5  is copied in ‘Sheet1’, though our current worksheet is ‘Sheet2’.

Read More: Cell Reference in Excel VBA (8 Examples)

2. Copy a Cell Reference in Another Sheet for Multiple Ranges with Excel VBA

Similar to reference a cell, you can reference multiple ranges too. For example, we want to copy the range B4:F11 from ‘Sheet3’. To do so, follow the simple outlined steps.

Step 1:

• After selecting a new Module, paste the following VBA.

Sub Copy_Ranges()
    Worksheets("sheet3").Range("B4:F11").Copy
End Sub

Step 2:

• Then, save the program and press  F5  to run it.
• As a result, you will observe that the range B4:F11 is copied.

Step 3:

• Furthermore, for copying multiple ranges in another sheet just type the ranges separated by a comma as the below code is shown. Simply paste the code and run.

Sub Copy_Multiple_Ranges()
    Worksheets("sheet3").Range("B4:F11,B13:F16").Copy
End Sub

• Consequently, the ranges B4:F11 and B13:F16 are selected and copied separately.

Read More: Reference Another Sheet in Excel (3 Methods)

Similar Readings

• Excel VBA: R1C1 Formula with Variable (3 Examples)
• How to Reference Text in Another Cell in Excel (14 Ways)
• How to Find and Replace Cell Reference in Excel Formula
• [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)
• How to Use FormulaR1C1 Absolute Reference in Excel VBA

3. Paste a Cell Reference in Another Sheet with Excel VBA

In the section, we will show how to reference cells in another sheet to paste them. In this example, we will copy the range B2:F11 from ‘Sheet4’ and paste them in ‘Sheet5’. Follow the instruction below to accomplish the task.

Step 1:

• Paste the following VBA code after selecting a new Module.

Sub Paste_Multiple_Ranges()
    Sheets("Sheet4").Range("B2:F11").Copy
Sheets("Sheet5").Range("B2:F11").PasteSpecial
End Sub

Step 2:

• Then, press  F5  to run the program after making sure you have saved the program.
• Therefore, the range of ‘Sheet4’ will be pasted in ‘Sheet5’.

Read More: Different Types of Cell References in Excel (With Examples)

4. Apply Cell Reference for Another Workbook with Excel VBA

It is a very important section, as we will apply cell references for different workbooks. We will copy the data from the workbook ‘Fill Blank Cells’ and the worksheet named ‘VBA’. Then, we will paste it in ‘Sheet7’ of our current workbook ‘Cell Reference’. Follow the steps below to have done the cell reference.

Step 1:

• Select a new Module and paste the following VBA.

Sub Copy_from_Another_Workbook_1()
    Workbooks("Fill Blank Cells.xlsm").Worksheets("VBA").Range("B4:F14").Copy
    Sheets("Sheet7").Range("B4:F14").PasteSpecial
End Sub

Step 2:

• To run the program, press  F5  after saving.
• As a result, you will see the copied cells from another workbook.

• Besides, the pasted cells in the current workbook in ‘Sheet7’.

Read More: How to Use Cell References in Excel Formula (All Possible Ways)

Conclusion

To summarize, I hope you now understand how to refer to cells in another sheet with Excel VBA. All of these tactics should be used to teach and practice with your data. Examine the practice book and put what you’ve learned into practice. Because of your vital support, we are motivated to continue presenting lectures like this.

If you have any questions, please do not hesitate to contact us. Please leave your opinions in the space below.

The Exceldemy staff will respond as soon as possible.

Stay with us and keep learning.

Related Articles

• How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)
• Absolute Cell Reference Shortcut in Excel (4 Useful Examples)
• Mixed Cell Reference in Excel (4 Examples)
• Absolute Cell Reference in Excel (4 Examples)
• How to Lock a Cell in Excel Formula (2 Ways)
• Excel Sheet Name in Formula Dynamic (3 Approaches)