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.

Sample Data

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.

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

Step 1:

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

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

Step 2:

  • Paste the following VBA.
Sub Select_a_Cell()
    Worksheets("sheet1").Range("D5").Copy
End Sub

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

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

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

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

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

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.

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

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

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

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

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

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


Similar Readings


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.

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

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

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

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

Sample Data

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.

Sample Data

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

Sample Data

Step 2:

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

Sample Data

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

Sample Data

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

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo