Passing Reference as new Cell Value

[ExcelWizards]

Okay so my macro copies a row, goes to the sheet where the row will be pasted, finds the the first open row and pastes it there.
If the number in the 4th column is 0 I want it to instead of copying over the 0 to set Cells(new.Row, 4) to be a reference to where I column 4 of where I copied it.

I'm not sure how to use the Indirect function inside a macro? Please help.

[hafizimran]

Please post your code so that it might be edited.

[ExcelWizards]

newst = "= ('" & thesheet.name & "'!D" & therow & ")"

Is coming out perfect except: therow has apostrophe's around it =('Rightsheet'!'D19')

My code wasn't the problem it was the concept. There is some code for you, please help.

This is what I'm using, it could be the wrong function, yes?

Cells(space, 4).FormulaR1C1 = newst

[hafizimran]

Sub CopyPaste()
    Dim strAddress As String    'for storing the source rows 4th cell address
    Dim tr As Long  'for storing the target sheets' last row number
    'Change the sheet name and row number that is being copied
    strAddress = Sheets("Sheet1").Rows("2").Range("D1").Address
    
    'I suppose that your target sheet is Sheet2
    tr = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Sheets("Sheet1").Rows("2").Copy
    Sheets("Sheet2").Cells(tr, 1).PasteSpecial
    Application.CutCopyMode = False
    'After pasting
    If Sheets("Sheet2").Range("D" & tr).Value = 0 Then
        Sheets("Sheet2").Range("D" & tr).Formula = "=Sheet1!" & strAddress
    End If
End Sub

[ExcelWizards]

YES! I meant to use Formula, FormularR1C1 was what was messing it up!