Hi Thomas,

Try this:

Sub SettInnNy()
With Workbooks("data.xls")
.Sheets("Ny").Copy Befo=.Sheets(1)
wsName = .Sheets(1).Name
End With
With Workbooks("innretninger.xls").Sheets("innretninger ")
.Rows(108).Insert
.Range("H108").FormulaR1C1 = "='[data.xls]" & _
wsName & "'!R12C5"
End With
End Sub


Regards,
KL



"Thomas F" wrote in message
...
Hi,

I am fairly new to programming in VBA/Excel, and find myself having
trouble with the most seemingly straightforward things...

I am working on a macro that creates a new worksheet and a new row in an
existing worksheet, and links a cell in the new row to a cell in the new
worksheet.

The relevant code (from a recorded macro) is as follows:

Sub SettInnNy()
Windows("innretninger.xls").Activate
Sheets("innretninger").Select
Range("B108").Select
Selection.EntireRow.Insert
Windows("data.xls").Activate
Sheets("Ny").Copy Befo=Sheets(1)

Windows("innretninger.xls").Activate
Range("H108").Select
ActiveCell.FormulaR1C1 = "='[data.xls]'Sheets(1)!R12C5"
End Sub


It gives the following error message:

<Run-time error '1004': Application-defined or object-defined error


When recorded the last line is

ActiveCell.FormulaR1C1 = "='[data.xls]Ny (2)'!R12C5"

which works fine the first time, but the second and third time keeps on
referring to the same sheet, when it should refer to the newest one. It
is obviously my modification that isn't working
-- any help on how to do this would be greatly appreciated.

Thomas

*** Sent via Developersdex http://www.developersdex.com ***