Help wirting this ARRAY (1951 1952 1953........2008)

[NeverHappyMike]

Hi


I've inherited a workbook that has lots and lots of yearly datasheets (and we keep adding to them). I'm in the process of amending a macro to recalculate the data in each sheet, but I wondered if there is a more suitable way to write an array for assigning the worksheets to a year, rather having to hard code it each time?


Many thanks for your help.

Michael.


Currently the macro includes:

[pre]

Dim year as variable, y as variable
Year  = Array (1951"", "1952", "1953",........"2008"....)
----THE YEARS ARE HARD CODED AND ADDED TO EACH TIME A NEW YEARLY WORKSHEET IS ADDED
For Each y In year
Sheets(y).Activate
etc, etc.

[/pre]

 

[keymaster]

why create an array in the first place?


you can

[pre]

For y=1951 to 2008
Sheets(y&"").Activate
etc, etc.
next y

[/pre]

 

[NeverHappyMike]

Good question. I'll give your code a try and see where it takes me.


Many thanks for your quick response.


Mike


P.S. Great website by the way.

 

[JP]

This response is late, but hopefully it will help someone.


What I would do is designate a separate worksheet for the array. Fill the years down in column A (although it really doesn't matter what column you use). Create a dynamic name for the array. For example, this range name assumes that the sheet is called "Misc Data" and the list of years starts in cell A2 (with A1 being a header).


=OFFSET('Misc Data'!$A$1,1,0,COUNTA('Misc Data'!$A:$A)-1,1)


For argument's sake I'll name the range "Years".


Three things have just happened:


1. You can refer to Worksheet.Range("Years") in your code and it will always point to the full list of years on the worksheet. For example, to create the array, just use


Dim years As Variant

years = Range("Years").Value


2. You've removed the hardcoded years from the macro code, but most importantly:

3. Anyone can come along and add a new year to the worksheet and run your code and it will auto-update itself to include the new year.


To me, #3 is the most important because you don't have to touch the code in order to update the range of data the macro acts on. And now you can go on vacation (or get promoted) without your boss worrying about re-running the report, or you worrying about someone having to touch the underlying VBA while you're gone!

 

[VaraK]

I thought, I'll share my thoughts too. I guess there is no need for hardcoding of years in vba code or in a seperate sheet.


The below code searches for worksheet names from 1951 to current year and runs your code. Hope this helps.


Sub test()

Dim i As Long, j As Long


i = Year(Now()) ' Gets the current year

j = ThisWorkbook.Worksheets.Count


For j = 1 To j 'All the worksheets in the activeworkbook

For i = 1951 To i 'Since we have 1951 as the start of the year

'Finds the worksheet name with an year between 1951 to current year

If Worksheets(j).Name = i Then

'add your code here

Worksheets(j).Activate

'etc etc etc

End If

Next i

Next j

End Sub


Thanks.