TamirBasin
New Member
- Joined
- Apr 11, 2017
- Messages
- 49
- Office Version
- 365
- Platform
- Windows
Hi,
When I run my macro, I have to activate the correct sheet before updating the range of the Named Range.
Therefore, I thought to activate the relevant sheet of the currently named range.
I get an error code.
I would appreciate the help.
Is there another way to do it?
When I run my macro, I have to activate the correct sheet before updating the range of the Named Range.
Therefore, I thought to activate the relevant sheet of the currently named range.
I get an error code.
I would appreciate the help.
Is there another way to do it?
Code:
Sub UpdateRange2()
Dim NamedRange As Name
Dim ColumnOfRange As Long
Dim LastRow As Long
Dim wb As Workbook
Dim ws As Worksheet
i = 1
Set wb = ActiveWorkbook
Debug.Print ActiveWorkbook.Names.Count
For Each nm In ActiveWorkbook.Names
Set NamedRange = wb.Names.Item(i)
//The next line gives me the error
ws = NamedRange.RefersToRange.Parent.Name
ColumnOfRange = Range(wb.Names.Item(i)).Column
// The next line refers the ColumnOfRange (which is a number) to the active sheet.
//This is why I need to be on the sheet of the specific Named Range.
LastRow = Cells(Rows.Count, ColumnOfRange).End(xlUp).Row
NamedRange.RefersTo = NamedRange.RefersToRange.Resize(LastRow, 1)
Debug.Print nm.Name, nm.RefersTo
' MsgBox NamedRange.Name & vbLf & NamedRange.RefersTo & "Range Number "
i = i + 1
Next nm
End Sub