How to refernece a range in a different worksheet

[Ody]

I am stumped. How do you reference a cell or range in another worksheet?

For example, from sheet 15 I want to reference the cell J1 in sheet 1.

Is this possible? What does the code look like? Thanks!

 

[Andrew Poulsom]

With a formula?

=Sheet1!J1

The easiest way to get the syntax is to build the formula by pointing with your mouse.

 

[Jonmo1]

Try

='sheet 1'!J1


Or, from sheet 15, press =
Then click on the tab for sheet 1 and click cell J1 and press Enter.

 

[Ody]

Sorry, I meant what was the VBA for referencing a cell or range in another sheet so that it's a dynamic reference and not static?

Sheet15.Range("J1").Copy Destination:=ActiveCell

That copies it for me but I need it to update if that cells information changes.

 

[Andrew Poulsom]

You will have to enter the formula in the target cell in your VBA code. Record a macro while doing it manually to get the syntax.

 

[Ody]

Ok thanks!

One alternate to my question.

What is the syntax if I wish to always reference the last sheet in my workbook so that when I add worksheets I can create one macro instead of having to constantly reference the new sheets?

Thank you!

 

[Andrew Poulsom]

This puts a formula that references A1 on the last sheet in A1 on Sheet1:

Sub Test()
    Dim LastSheet As String
    LastSheet = Worksheets(Worksheets.Count).Name
    Worksheets("Sheet1").Range("A1").Formula = "='" & LastSheet & "'!A1"
End Sub

 

[Ody]

Andrew, thank you very much for your help!