Insert Names Macro

[derickc]

I'm trying to create a macro that will insert the column heading as a name for the entire selection in rows 7 through 1310 beneath each column heading (which is in Row6). When trying the following, I get an "invalid name" error message. Any clues? Thank you.


Sub InsertName()
'
' InsertName Macro
' Macro recorded 8/29/2002 by Derick Cooper
'

'

Dim lngCol As Long

For lngCol = 5 To 10
Range(Cells(7, lngCol), Cells(1310, lngCol)).Select
ActiveWorkbook.Names.Add Name:=Cells(6, lngCol).Text, RefersToR1C1:= _
"='Standard Deviations'!R7C[lngCol]:R1310C[lngCol]"

Next lngCol

End Sub

 

[derickc]

The problem is apparently in the following code:

"='Standard Deviations'!R7C[lngCol]:R1310C[lngCol]"

 

[derickc]

This is the speghetti theory of coding. I just keep changing things til something works. I changed to from the R1C1 reference style to this: RefersTo:= "='Standard Deviations'!Range(Cells(7,lngCol), Cells(1310, lngCol))"

Which has apparently worked. When I look under names/define all my new names are there, but when I try to "Goto" one of the new names it isn't recognized.

Any clues?

-dc

 

[Jim North]

change:

"='Standard Deviations'!R7C[lngCol]:R1310C[lngCol]"

to:

"='Standard Deviations'!R7C" & lngCol & ":R1310C" & lngCol