Resize all named ranges in a workbook using VBA (add columns)

[farrukhikram]

Hello guys,

I have hundreds of named ranges in each excel file and I have hundreds of excel files to deal with (all with different named ranges). I am looking for a VBA which (without a specific pattern), resizes ALL the named ranges and just add 12 columns to each range. (All the ranges contain only a single row), only horizontal expansion is required.

I found another thread here in the same forum, following is a link the that thread as well:

Click here to see the other thread

Note: I am a beginner / NOVOICE in VBA programming so a detailed help will be appreciated (like where to paste the macro as well).

 

[farrukhikram]

I think the other thread got deleted (the link above is not working anymore), so I am pasting my question from the other thread here as well:

Hello Guys,

After a lot of research on internet, at last I got to this question which is exactly what I am trying to do. Just to explain the issue further in following steps:

1. I pasted the following code from above in 'ThisWorkbook' tab VBA editor:

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

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

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

4. After the 'For' statement, The strName on mouse shows the error "<Object variable or with block variable not set>

5. 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?