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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>