How to refer to programmatically Dynamice Range names
 

Hello,

I need help with the dynamic range names created programmatically.

Follwing code generates the range names! When I tries to refer them as
"workbook.xls"!"rangename" I get error message 'refence is not valid' !

I am unable to figure this! I need to use all these range names in charts,
which I intend to create programmatically.

Thanks

Mike

Sub DynamicRangeNames()

Dim intRow_Num As Integer
Dim strRangeName As String
Dim ch As ChartObject, x
Dim SearchString, SearchChar, MyPos As Integer
Dim strSheetName As String, strCoverSheet As String
Dim strSheetNameNew As String
Dim isheet As Integer, i As Integer, j As Integer
Dim iRowNum As Integer, iColNum As Integer
Dim iReportRowNum As Integer, iReportColNum As Integer
Dim strMonthName As String, strPrevMonthName As String
Dim imonth As Integer, intNewGroup As Integer
Dim strYearname As String, strReportName As String


For i = 1 To ActiveWorkbook.Sheets.Count

strSheetName = ActiveWorkbook.Sheets(i).Name
strSheetNameNew = strSheetName
intNewGroup = 4
Select Case strSheetName

Case "Cover Sheet", "Template", "Old Template", "Charts"
'Do nothing, ignore this worksheets
Case Else

For intRow_Num = 3 To 36
If IsEmpty(Sheets(strSheetName).Cells(intRow_Num,
1)) Then
intNewGroup = intRow_Num + 1
intRow_Num = intRow_Num + 1
Else
intRow_Num =
Sheets(strSheetName).Cells(intRow_Num, 1).Row
strRangeName =
Trim(Sheets(strSheetName).Cells(1, 1).Value) & "_" & _
Trim(Sheets(strSheetName).Cells(intNewGroup,
1).Value) & _
"_" &
Trim(Sheets(strSheetName).Cells(intRow_Num, 1).Value)
Do
MyPos = InStr(1, strRangeName, " ",
vbBinaryCompare)
If MyPos 0 Then
Mid(strRangeName, MyPos, 1) = "_"
End If
Loop Until MyPos <= 0

Do
MyPos = InStr(1, strRangeName, "-",
vbBinaryCompare)
If MyPos 0 Then
Mid(strRangeName, MyPos, 1) = "_"
End If
Loop Until MyPos <= 0

Do
MyPos = InStr(1, strSheetNameNew, " ",
vbBinaryCompare)
If MyPos 0 Then
Mid(strSheetNameNew, MyPos, 1) = "_"
End If
Loop Until MyPos <= 0

Do
MyPos = InStr(1, strSheetNameNew, "-",
vbBinaryCompare)
If MyPos 0 Then
Mid(strSheetNameNew, MyPos, 1) = "_"
End If
Loop Until MyPos <= 0
ActiveWorkbook.Names.Add Name:=strRangeName,
RefersTo:= _
"=OFFSET(" & strSheetNameNew & "!" &
Sheets(strSheetName).Cells(intRow_Num, 1).Address & _
",0,1,,COUNTA(" & strSheetName & "!$" & _
intRow_Num & ":$" & intRow_Num & ")-1)"
End If
Next
End Select
Next

End Sub

How to refer to programmatically Dynamice Range names
 

Why are you wanting to use the workbook name?

If you mean worksheet name, use

Worksheets("worksheet_name").Range("range_name")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike Metal" wrote in message
. com...
Hello,

I need help with the dynamic range names created programmatically.

Follwing code generates the range names! When I tries to refer them as
"workbook.xls"!"rangename" I get error message 'refence is not valid' !

I am unable to figure this! I need to use all these range names in charts,
which I intend to create programmatically.

Thanks

Mike

Sub DynamicRangeNames()

Dim intRow_Num As Integer
Dim strRangeName As String
Dim ch As ChartObject, x
Dim SearchString, SearchChar, MyPos As Integer
Dim strSheetName As String, strCoverSheet As String
Dim strSheetNameNew As String
Dim isheet As Integer, i As Integer, j As Integer
Dim iRowNum As Integer, iColNum As Integer
Dim iReportRowNum As Integer, iReportColNum As Integer
Dim strMonthName As String, strPrevMonthName As String
Dim imonth As Integer, intNewGroup As Integer
Dim strYearname As String, strReportName As String


For i = 1 To ActiveWorkbook.Sheets.Count

strSheetName = ActiveWorkbook.Sheets(i).Name
strSheetNameNew = strSheetName
intNewGroup = 4
Select Case strSheetName

Case "Cover Sheet", "Template", "Old Template", "Charts"
'Do nothing, ignore this worksheets
Case Else

For intRow_Num = 3 To 36
If IsEmpty(Sheets(strSheetName).Cells(intRow_Num,
1)) Then
intNewGroup = intRow_Num + 1
intRow_Num = intRow_Num + 1
Else
intRow_Num =
Sheets(strSheetName).Cells(intRow_Num, 1).Row
strRangeName =
Trim(Sheets(strSheetName).Cells(1, 1).Value) & "_" & _

Trim(Sheets(strSheetName).Cells(intNewGroup,
1).Value) & _
"_" &
Trim(Sheets(strSheetName).Cells(intRow_Num, 1).Value)
Do
MyPos = InStr(1, strRangeName, " ",
vbBinaryCompare)
If MyPos 0 Then
Mid(strRangeName, MyPos, 1) = "_"
End If
Loop Until MyPos <= 0

Do
MyPos = InStr(1, strRangeName, "-",
vbBinaryCompare)
If MyPos 0 Then
Mid(strRangeName, MyPos, 1) = "_"
End If
Loop Until MyPos <= 0

Do
MyPos = InStr(1, strSheetNameNew, " ",
vbBinaryCompare)
If MyPos 0 Then
Mid(strSheetNameNew, MyPos, 1) =
"_"
End If
Loop Until MyPos <= 0

Do
MyPos = InStr(1, strSheetNameNew, "-",
vbBinaryCompare)
If MyPos 0 Then
Mid(strSheetNameNew, MyPos, 1) =
"_"
End If
Loop Until MyPos <= 0
ActiveWorkbook.Names.Add Name:=strRangeName,
RefersTo:= _
"=OFFSET(" & strSheetNameNew & "!" &
Sheets(strSheetName).Cells(intRow_Num, 1).Address & _
",0,1,,COUNTA(" & strSheetName & "!$" & _
intRow_Num & ":$" & intRow_Num & ")-1)"
End If
Next
End Select
Next

End Sub