Paste Location of Cell with VBA?

[theteerex]

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.

How would I go about modifying my code, highlighted in red to add this functionality?

Current 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 


[B][COLOR=#ff0000]Sheets("Projects").Cells(LastRow, "B").Copy Sheets("Database").Cells(Newproject - Masterrow + 1, "C")[/COLOR][/B] 


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

 

[MARK858]

I tried it and none of it worked.
I am trying to define CellOne and CellTwo properly and nothing has worked so far.

Dim CellOne As Range
Set CellOne = Worksheets("Projects").Range("Records").Cells(LastRow, "B").Address

did not work.

When I hover over Cellone, excel tells me it is nothing.

.Address makes it a String not a Range so you haven't defined it "properly".

 

[theteerex]

.Address makes it a String not a Range so you haven't defined it "properly".

I am changing my approach completely since I found out I can actually paste special link.
Could I please get some help fixing this?:

With Sheets("Projects")
    .Range("B" & Rows.Count).End(xlUp).Copy
[COLOR=#ff0000]    Sheets("Database").Cells(Newproject - Masterrow, "C").PasteSpecial Link:=True[/COLOR]
End With

I got a run-time error 1004
Application defined or object defined error.

 

[MARK858]

So what are the values of Newproject and Masterrow when you get the error and please post your full code rather than an excerpt (btw why use a With statement when you are only using it once?).

 

[theteerex]

So what are the values of Newproject and Masterrow when you get the error and please post your full code rather than an excerpt (btw why use a With statement when you are only using it once?).

My full code is already posted in the original post.
CTRL + G gives me nothing when I run it after error message.

I thought with was the best way to run it since it already worked with my paste special formats in my code.

 

[MARK858]

My full code is already posted in the original post.

Your original post did not include the Link:=True for a start and so it is not the same as you are using now.
Again post your full current code and answer the question I asked which was

So what are the values of Newproject and Masterrow when you get the error