I'm trying to make my worksheet generate a bunch of named ranges whenever it is activated.
However, I'm getting a "Not a valid name" error on the line in red. The value of r is a string, so I really don't see what the issue is. Another time, I got a object defined error, but I think I've declared everyhing too. That line is very similar to the line above, and tends to work once (the first time around the loop). After that, I get the errors I mentioned.
Private Sub Worksheet_Activate()
Set wb1 = ActiveWorkbook
Set ws1 = wb1.Worksheets("Data")
Set ws2 = wb1.Worksheets("Summary")
Set ws3 = wb1.Worksheets("Cumulative Number of LDPs")
Set ws4 = wb1.Worksheets("Setup")
Dim found_proj As Range
Dim end_row_p As Integer
Dim end_row2 As Integer
end_row2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row
'create named range for projects
wb1.Names.Add Name:=("Proj_List"), _
RefersTo:=ws4.Range(ws4.Range("AA4"), ws4.Range("AA4").End(xlDown))
'create named range for subsystems in each project
For Each r In ws4.Range("Proj_List")
Set found_proj = ws2.Range("A:A").Find(What:=r, LookAt:=xlWhole)
If Not found_proj Is Nothing Then
If ws2.Range("A" & found_proj.Row + 1) <> "" Then
end_row_p = found_proj.Row
ElseIf ws2.Range("A" & found_proj.Row).End(xlDown).Row > end_row2 Then
end_row_p = end_row2
Else
end_row_p = ws2.Range("A" & found_proj.Row).End(xlDown).Row - 1
End If
wb1.Names.Add Name:=(r), _
RefersTo:=ws2.Range(ws2.Range("B" & found_proj.Row), ws2.Range("B" & end_row_p))
End If
Next r
End Sub
If anyone could tell me what i'm doing wrong, I'd be greatful.
Bookmarks