Pasting the Location of a Cell in Another Worksheet - Excel VBA

Question

Here is how my code currently works:

On sheet 1 at location B5, the value of the cell is Dog. On sheet 2, C15, the paste location, the value is also Dog.

What I would like is for C15 to be =$B$5. This way, I can change B5 on just sheet 1 and C15 on sheet 2 changes as well.

I thought I could use a paste special but can't find any that would work since it's not really a paste function.

I thought I could maybe use this:

Sheets("Projects").Range(LastRow, "B").Value =_ 
Sheets("Database").Range(Newproject - Masterrow + 1, "C").Value

But it did not work, and so I'm here...

Current code: Code:

Sub FindProjectName() 
Dim LastRow As Long
Dim Newproject As Long
Dim MasterTemplate As Range
Dim Masterrow As Long


'MasterTemplate is the database entry template.
Masterrow = Worksheets("Database").Range("MasterTemplate").Rows.Count 
LastRow = Sheets("Projects").Cells(Rows.Count, "B").End(xlUp).Row 
Newproject = Sheets("Database").Cells(Rows.Count, "C").End(xlUp).Row 


Sheets("Projects").Cells(LastRow, "B").Copy Sheets("Database").Cells(Newproject - Masterrow + 1, "C") 


With Sheets("Database")
    .Range("DBASE").Rows(1).Copy 
    .Range("DBASE").Rows(Newproject - Masterrow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
End With
End Sub

I can't just use =SheetName!B5 for example because the template is copied to a new location everytime the code runs. I tried that.

Answer 1

The code below will accomplish what you described in the first part of your question. Once you understand how it works, I think you'll be able to apply it to your situation. If not, feel free to ask questions.

Sub formulaTest()
Dim sh1 As Worksheet, r1 As Range, r2 As Range
Set sh1 = Worksheets("Sheet1")
Set r1 = sh1.Range("B5")
r1 = "Dog"
Set r2 = Worksheets("Sheet2").Range("C15")
r2.formula = "=" & sh1.Name & "!" & r1.Address
End Sub

Here's the code you supplied in a followup comment along with an animated gif showing it working (except for overwriting the last item, "6")

Sub formulaTester()
Dim Feeder As Worksheet
Dim OneCell As Range
Dim TwoCell As Range
Set Feeder = Sheets("Projects")
Set OneCell = Feeder.Range("B" & Rows.Count).End(xlUp)
OneCell = "Cow"
Set TwoCell = Sheets("Tester2").Range("C17")
TwoCell.formula = "=" & Feeder.Name & "!" & OneCell.Address
End Sub