VBA .range that iterates thru worksheets

[Bigced_21]

I'm trying to figure out how to write this using advanced vba coding. The original way i wrote this code was extremely large and repetitive. I'm wondering if someone can show me an example of how to create a ".Range formula" this loops thru 13 identical worksheets. This worksheet will be created every month, and also the past month totals (if exist) will need to be retrieved and pulled over to the current month spreadsheet worksheets and the Summary worksheet of all 13 worksheets.
I have a idea of my coding its the if statement where i get hung up yet, because i don't understand how to write the range formula for what i want to do.

here is the ranges of the totals i want to start out capturing:
B4:J4; B7:J7; D12; B19:M19; ETC

this is consistent on all 13 worksheets. Here's the code I've written thus far:

Please Login or Register  to view this content.

[rylo]

Hi

Looping through the sheets is probably the easy part. Something like

Please Login or Register  to view this content.

However, as your ranges are not consistent, I don't see any easy way to loop through them all. But a couple of thoughts.

1) Build arrays to hold the row, the starting column and the ending column.

So the first array would contain things like 4, 7, 12, 12, 19...
Second would contain the starting column - 2, 2, 4, 7, 2
Third would contain the ending column: 11, 11, 4, 7, 14...

The arrays would have to be exactly equal. Notice that there are 2 rows 12. This is because there is only a 1 column range (D and G). So you have a starting and ending column of 4 and 4 for D, and 7 and 7 for G.

Another way would be to build a 3 dimension array that contains all the same details in the one line of the array. Same sort of thing.

When it comes to the formula, then I think you should be able to build it using the array column number. Not sure if the B2 is fixed for all formulas tho.

HTH

rylo