relative cell references

[Thomas F]

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 Before:=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 ***

 

[KL]

Hi Thomas,

Try this:

Sub SettInnNy()
With Workbooks("data.xls")
.Sheets("Ny").Copy Before:=.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]

Thanks a million, KL -- not only did it work, it also gave me some faith
in VBA. Good to see that things can be done more elegantly than what
comes out of the record-macro function.

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

 

[KL]

You're welcome. And yes, the macro recorder is good for quick'n'dirty code,
whcih then has to be tidied up for optimal performance. It is also a great
tool to quickly identify objects, properties, methods, etc. you may need to
use.

KL