You can either use .cells() or .range(), but you can't mix and match:

With Sheets("Table")
LastusedrowinD = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range("D" & LastusedrowinD).Value = _
"=Cell(""filename"",'" & SheetName & "'!$A$1)"
End With

or

With Sheets("Table")
LastusedrowinD = .Cells(.Rows.Count, "D").End(xlUp).Row
.cells(lastusedrowinD,"D").Value = _
"=Cell(""filename"",'" & SheetName & "'!$A$1)"
End With

======
And watch your quotes surrounding "Filename". You have to double up on them in
VBA to get single "'s in the worksheet formula.

And one more question...

Did you really want to overwrite that last used cell in column D.

I would have guessed:
.Range("D" & LastusedrowinD + 1).Value = ...
or
.cells(lastusedrowinD + 1, "D").Value = ...

Or change the variable name to NextRowToUseInD and use:
NextrowtouseinD = .Cells(.Rows.Count, "D").End(xlUp).Row + 1




Karen53 wrote:

Hi,

I am trying to insert a formula into a cell and am having trouble. I am
substituting the variable 'sheetname' for 'Sheet1'.

Here's my code:

Sub AddToList(SheetName)

Dim LastusedrowinD As Long

'The Formula is =Cell("filename" ,'Sheet1'!$A$1)

With Sheets("Table")
LastusedrowinD = .Cells(.Rows.Count, "D").End(xlUp).Row
.cell("D" & LastusedrowinD).Value = _
"=Cell(" & "filename" & ",'" & SheetName & "'!$A$1)"
End With

End Sub

Any suggestions as to how I would add this formula into the cell?

Thanks.

--

Dave Peterson