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

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" <thomas@devdex.com> wrote in message
news:%23NXq7n3iFHA.320@TK2MSFTNGP09.phx.gbl...
> 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 ***

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

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" <thomasf@devdex.com> wrote in message
news:eQGj8PGjFHA.3692@TK2MSFTNGP09.phx.gbl...
> 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 ***