Looping through a series of named ranges - Excel VBA

[samkeenan]

Hi All,

I have 52 named ranges that I wish to loop through in a vba routine
("Week1", "Week2", .... up to "Week52")

I thought I could loop through all the "Week" ranges as follows:
============
For cloop = 1 To 52
weekrange = "Week" & cloop
Range(weekrange).Select
=============

However I get the following 1004 error:
Select Method of Range Class failed

This message sometimes comes up on the first loop ("Week1") but sometimes on the second ("Week2"), so could it be something to do with active cells?

The ranges are spread across 4 different worksheets

Any help greatly appreciated!!

 

[hippiehacker]

For cloop = 1 To 52
weekrange = """ & "Week"  & cloop & """
Range(weekrange).Select

 

[hippiehacker]

sorry this should work

weekrange = "Week" & cloop
Range(weekrange).Select

not sure why its not working for you? is the active sheet the sheet where the names are hosted?

 

[samkeenan]

sorry this should work

weekrange = "Week" & cloop
Range(weekrange).Select

not sure why its not working for you? is the active sheet the sheet where the names are hosted?

Thanks hippiehacker.

I got round the problem by activating the sheet each named range was on. That did the trick, but I didn't realise that would be neccessary with named ranges.