VBA to extend multiple/all named ranges in a workbook

Question

I am trying hard since last weekend to work with a code for extending ALL named ranges in a workbook, but it always gives error. Following is where I have reached so far, any help will be highly appreciated:

    Sub ResizeNamedRange()
    Dim xWb As Workbook
    Dim xName As Name
    Set xWb = Application.ActiveWorkbook
    Set xName = xWb.Names.Item
    
    For Each xName In ActiveWorkbook.Names

    With xName
        .RefersTo = .RefersToRange.Resize(, 14)
    End With
    Next
    End Sub

I have also tried the following code:

Sub AdjNamedRanges_New()
    Dim nRange          As Name
    Dim strName         As String
    Dim ans As Long
For Each nRange In ActiveWorkbook.Names
    strName = nRange.Name
    With ActiveWorkbook.Names.Item(strName)
    ans = Range(strName).Columns.Count
        
        .RefersTo = .RefersToRange.Resize(, ans + 12)
    
    End With
Next nRange
End Sub

1. When I ran this Macro, it gave me error '400'

2. When I press 'F8', both 'nRange' and 'strName' are showing 'File Name' (The current file name) on mouse over.

3. After the 'For' statement, The strName on mouse shows the error "

4. I have tried by pasting the above code in a new module as well as in the active sheet as well, but in vain.

Can you please tell me what I am doing wrong?

Answer 1

Dears,

For all those who are experiencing same issue, I was able to resolve it by the following macro:

Sub MonthlyReporting()
    Dim nRange          As Name
    Dim strName         As String
    Dim ans As Long
  
For Each nRange In ActiveWorkbook.Names
On Error Resume Next
    strName = nRange.Name
    With ActiveWorkbook.Names.Item(strName)
    ans = Range(strName).Columns.Count
        
        .RefersToR1C1 = .RefersToRange.Resize(, ans + 12)
    End With
Next nRange

End Sub