Looping through Worksheet, and assigning named ranged of worksheet range to Worksheet name

Question

I have a workbook book that has worksheets for each state in the US.

I want to loop through each worksheet, and assign a named range for the data in the table to be the worksheet name.

Right now I have it set up where it is looping through everysheet, skipping the sheet I want it to skip, and setting the proper range. However, it ended up creating a 50 named ranges for the first sheet.

Sub Sheetloop()
Dim ws As Worksheet
Dim rng As Range
Dim ws_name As String
For Each ws In Worksheets
    If ws.Name <> "GA_AVERAGE" Then
       ws_name = ws.Name
       Set rng = Range("A2:N" & Range("A" & Rows.Count).End(xlUp).Row)
       rng.Name = ws_name
    End If
Next
End Sub

I expect each sheet to has its respective namedrange attached to the data within the sheet.

Answer 1

You are not referencing the sheet each time, so you need.

ws.Range("A2:N" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)