Excel VBA: Loop for setting dynamic named ranges in worksheets

Question

I need to loop trough each worksheet in my workbook and set dynamic named ranges in each one of them, except worksheet Summary Report. I need to name the ranges with the worksheet name and "Close".

I currently have this:

Option Explicit
Public Sub DefineNamedRanges()

    Dim WSheet As Worksheet

    Dim ShtName As Variant

    For Each WSheet In Worksheets

        ShtName = WSheet.Name

        If ShtName <> "Summary Report" Then

            WSheet.Names.Add Name:=ShtName & "Close", _
                RefersTo:="=OFFSET(ShtName!$A$2,0,0,COUNTA(ShtName!$A$2:$A$1048576),COUNTA(ShtName!$2:$2))"

        End If

    Next WSheet

End Sub

This code creates the ranges and names them as I want. When I open the Name Manager, it seems that the ranges are not referring to anything. It is like solely the names are created, but not the ranges themselves.

You need to move ShtName outside the quotation marks in the RefersTo and concatenate with &. — BigBen
@BigBen, works like a charm, but why did you delete your answer? — I. Я. Newb

BigBen BigBen · Accepted Answer · 2018-10-30T22:16:29

If ShtName is within the quotation marks, it's no longer a variable, but the text "ShtName", as evidenced by your screen shot.

Move all instances of ShtName outside the quotation marks and concatenate with &. You should probably add single quotes as well around the sheet name.

Change

"=OFFSET(ShtName!$A$2,0,0,COUNTA(ShtName!$A$2:$A$1048576),COUNTA(ShtName!$2:$2))"

to

"=OFFSET('" & ShtName & "'!$A$2,0,0,COUNTA('" & ShtName & "'!$A$2:$A$1048576),COUNTA('" & ShtName & "'!$2:$2))"

Excel VBA: Loop for setting dynamic named ranges in worksheets

1 Answers