Loop through worksheets

[gmscharl]

I have a workbook that has a summary page and an undetermined amount of worksheets. I'd like to iterate through the worksheets, updating a formula on the summary page. I've included a sample to help explain...

Basically, I'd like to get the average of all of the numbers in cell B2 for each worksheet and display the average on the summary tab. I will be adding tabs through the month that will need to be included on the summary tab. The format for all new tabs will remain the same, so each new tab will still reference cell B2.

Help is greatly appreciated...

[VBA Noob]

No need for code

See attached. Place any need sheets between the start and end sheets

VBA Noob

[piyushdabomb]

Here is the complete solution without specifying your start and end sheets:

Please Login or Register  to view this content.

[gmscharl]

Wow, that's pretty easy. I feel dumb.

Especially because I can't get the CountIF statement to work that I replaced Count with.

I get a #VALUE error. My goal is to count the number of times "Off" appears in that cell, but it's not as straight forward as I had hoped.

[gmscharl]

Please Login or Register  to view this content.

I understand all of the script, except what is quoted above...

what is r1.Formula doing?

Thanks!

[gmscharl]

One more thing, the function is writing on the summary tab, not the Temp_Sheet that is added. I tried to select:

Please Login or Register  to view this content.

and Activate

Please Login or Register  to view this content.

the Temp_Sheet for writing, but that didn't work.

[piyushdabomb]

The code should work. The Temp_Sheet tab will not work because I create it and then delete it because that temporary sheet is used to get you the average value spit out from a collection.

The statement:

r1.Formula = "=round(average(A1:A" & r1.Offset(-1, 0).row & "),2)"

essentially states: "In the temp_sheet tab, get the values from range A1 to the last item in the temporary sheet where all my values are pasted".

r1.offset(-1,0).row takes the row number of the data point right before you enter the formla.

[gmscharl]

I'm using Excel 2007 (would that make a difference?). The calculations are being written in the summary tab, not on the temp_sheet tab, see attached.

I've added the code you provided to this workbook. it should be under the VBScript section of excel. I put the curser on the first line and hit F5. That's when it provides the results on the summary tab. Maybe it's operator error?

Thanks

[gmscharl]

Ok, I figured it out. I added:
Set r1 = Worksheets("Temp_Sheet").Range("A1")

which made it write on Temp_Sheet instead of the summary tab.

I've attached a new spreadsheet. This is the actual one I'm using. There are 32 floors that I'm trying to get an average of. using the code provided, I did a VERY inefficient method to compute averages. It's ugly and I don't like it. There's got to be a better way to do this, but I've never used VB before this week. Please don't laugh...

Please Login or Register  to view this content.

[piyushdabomb]

Hehe. Apologies for not getting back to you earlier. Do you still need assistance?