Iteration naming range names in particular worksheets

G'day y'all,

I am trying to write some code which performs the following routines:

1. Remove any current Range Names
2. On certain worksheets, set some range names for an unknown number of rows (ie. they will change every month)

My code is below. For some reason, the While . . . Wend routine will only use the last used row number for the worksheet "Week 1". The Do Until, while the With . . . End loop seems to change focus to each of the 5 worksheets, seems to always stop when y= the last row of the active worksheet. So, it seems the With Worksheets() isn't changing focus to each individual sheet and I end up with the correct range names, but only based on the number of active rows in the active sheet.

Sub Week1()
'
' Week1 Macro
' Select and name the range for Weeks 1-5
'

'   Declare Variables
    Dim ShtNum As String
    Dim x, y, z As Integer
    Dim nm As Name

'   Remove all other Range Names in this Worksheet
    On Error Resume Next
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next

'   Set Variables for first part of the macro, which is to find the last non-empty row
    x = 1 ' column number to start count, this will count down in the referenced column to search for data (3 = C, 1 = A, 5 = E, etc.)
    y = 1 'row number ' row number to start on, if you have blank rows at the top you can start down lower like I did here

'   Find the last non-empty row in Column A
    z = 1
    While z < 6
        With Worksheets("Week " & z)
            Do Until Cells(y + 1, x).Value = ""
                y = y + 1
            Loop
        End With
'   Set the Named Range for Week 1
        Worksheets("Week " & z).Range("A1:A" & y).Name = "Week" & z
        z = z + 1
        y = 1
        x = 1
    Wend
    On Error GoTo 0
End Sub

Other important points:

• Excel 2010
• There should be no other Range Names used in the Workbook
• I want to use the Ranges in a summary sheet in SumIF() statements

Thank you,

Mitchies

You could replace that whole routine with a dynamic named ranges, like

Name: Week1 RefersTo: =OFFSET(Week1!$A$1, 0, 0, COUNTA(Week1!$A:$A), 1)

That's not VBA code, its a definition of a Named range.
Go to the Name Manger and enter that formula in the RefersTo section.
The OFFSET will start with Week1!$A$1 and will go down COUNTA(Week1!$A:A$) cells. That formulation assumes that you have no blank rows between data on that sheet.

You just enter that definition once and then, as data is added or removed (no blank rows), the range adjusts to those changes.