Excel VBA working with inactive Sheets

I am trying to create a macro to graph some data I have collected, by making a bunch of smaller macros to build up to this. One of the macros I am trying to create is as follows:
I have data from rows 5 to 370 in columns B to M. The macro I have written (as seen below) loops from row 5 to 370 and calculates an average of the data that it finds, I then want it to display that number in row 4; this will repeat all the way from column B to column M
The Problem: I have two sheets in this: “Turbidity” and “Color;” if “Turbidity” is open, it will display the result with no problem but it won’t work for “Color” and vice versa. I also get a pop up that says: Error1004 application-defined or object-defined error. I click “debug” and it highlights the row that starts with “Set Data” I can’t figure out what the problem is here. Any suggestions?????


Dim Turbdata As Range
Dim x As Integer
For x = 2 To 13
Set Turbdata = Worksheets("Turbidity").Range(Cells(5, x), Cells(370, x))
Worksheets("Turbidity").Cells(4, x).Value = WorksheetFunction.average(Turbdata)
Next x

' averages for color
Dim Coldata As Range
For x = 2 To 13
Set Coldata = Worksheets("Color").Range(Cells(5, x), Cells(370, x))
Worksheets("Color").Cells(4, x).Value = WorksheetFunction.average(Coldata)
Next x

Basically, why does this code work for only one sheet and not both?

That worked out brilliant! Thanks man!

You shouldn't have to activate a sheet in order to write data to a cell on that sheet. You also can do this with only 1 For-Next statement.

Please Login or Register  to view this content.