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

 Reference Cells in another Sheet with Excel VBA: 4 Methods

This is the sample data set.

Method 1- Reference Cells in Another Sheet with Excel VBA

Copy the data in D5 in ‘Sheet2’ to ‘Sheet1’

Step 1:

• Press  Alt + F11  to open VBA.
• Click Insert.
• Choose Module.

Step 2:

• Enter the following VBA.

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

Step 3:

• Save and press  F5  to run the code.
•  D5  is copied in ‘Sheet1’.

Read More: How to Display Text from Another Cell in Excel

Method 2 – Reference Cells for Multiple Ranges in another Sheet with Excel VBA

Step 1:

• Select Module and enter the following VBA.

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

Step 2:

• Save and press  F5  to run the code.
• The range B4:F11 is copied.

Step 3:

• Enter the following code.

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

• The ranges B4:F11 and B13:F16 are selected and copied.

Read More: How to Reference Text in Another Cell in Excel 

Method 3 – Reference Cells  in Another Sheet with Excel VBA

Copy the range B2:F11 from ‘Sheet4’ and paste it in ‘Sheet5’.

Step 1:

• Select Module and enter the following VBA:

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

Step 2:

• Save and press  F5  to run the code.
• The range in ‘Sheet4’ will be pasted in ‘Sheet5’.

Read More: How to Use Variable Row Number as Cell Reference in Excel

Method 4 –  Reference Cells in Another Workbook with Excel VBA

Copy the data in the ‘Fill Blank Cells’ workbook and the ‘VBA’worksheet. Paste it in ‘Sheet7’ of ‘Cell Reference’, the current workbook.

Step 1:

• Select Module and enter 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:

• Save and press  F5  to run the code.
• Cells will be pasted in the other workbook.

• Cells will also be displayed in ‘Sheet7’ of the current workbook.

Read More: How to Reference Cell by Row and Column Number in Excel

Download Practice Workbook

Practise in this workbook.

Related Articles

• How to Find and Replace Cell Reference in Excel Formula
• How to Use Cell Value as Worksheet Name in Formula Reference in Excel
• How to Use OFFSET for Cell Reference in Excel
• How to Reference a Cell from a Different Worksheet in Excel
• How to Reference Cell in Another Sheet Dynamically in Excel
• Excel VBA Examples with Cell Reference by Row and Column Number

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