Search code examples
excelvbadynamicnamed-ranges

Excel VBA: Loop for setting dynamic named ranges in worksheets

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.

Names Manager

Solution

  • 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))"