VBA for cell reference in MS Excel

hey there,

i am caught on a pretty easy coding...so i think it'll be.

here goes:

i am trying to define a named range for a dynamic cell reference in ms excel. i need to define the ending row and then select from R1C1 to (LastRow and Column 40 aka "AN") and name it "datapastedvalues".

I am a total neewbie at vba. thanks for the help.

Sheets("PCSL TRNX R3(UPLOAD)").Select
LastRow = Range("D1").End(xlDown).Row
ActiveWorkbook.Names.Add Name:="datapastedvalues", RefersToR1C1:= _
"=Sheet1!R1C1:LastRowC40"

i never heard back on this one.....does any one have any suggestions?

your help is greatly appreciated.

i should state the coding from above was copied from creating a macro......but the problem is that it end at row 40

Sheets("PCSL TRNX R3(UPLOAD)").Select
LastRow = Range("D1").End(xlDown).Row
ActiveWorkbook.Names.Add Name:="datapastedvalues", RefersToR1C1:= _
"=Sheet1!R1C1:LastRowC40" - is there a way to string this?

Tuk

here is the solution:

Sub Convert()
ActiveWindow.ActivateNext
ActiveSheet.Next.Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Next.Select
Application.CutCopyMode = False
Sheets("PCSL TRNX R3").Name = "PCSL TRNX R3"
ActiveSheet.Previous.Select
Sheets("Sheet1").Name = "PCSL TRNX R3(UPLOAD)"

lastrow = Cells.End(xlDown).Row
lastcol = Cells.End(xlToRight).Column


ActiveWorkbook.Names.Add Name:="data_pasted_values", RefersToR1C1:= _
"='PCSL TRNX R3(UPLOAD)'!R1C1:R" & lastrow & "C" & lastcol & ""
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.ActivateNext
End Sub