Iteration naming range names in particular worksheets

[Mitchies]

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.

Please Login or Register  to view this content.

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

[mikerickson]

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)

[Mitchies]

Thanks Mickerickson,

However, I can't get this to work - probably because I haven't worked with vba for a few years and even then, my use was not very in depth. What part of the code do I put this in? Is there a line above this code I need to insert also?

Thanks,

Neil

[mikerickson]

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.