relative cell references
 

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 ***

relative cell references
 

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 ***

relative cell references
 

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 ***

relative cell references
 

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

"Thomas F" wrote in message
...
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 ***