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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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: How to Display Text from Another Cell in Excel


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: How to Reference Text in Another Cell in Excel 


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: How to Use Variable Row Number as Cell Reference in Excel


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 Reference Cell by Row and Column Number in Excel


Download Practice Workbook

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


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.

Stay with us and keep learning.


Related Articles


<< Go Back to Reference to Another Sheet | Cell Reference in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo