CODEWITHSUNDEEP
excelvbaaverage
user3790770
user3790770

Reputation: 83

Excel macro for averages

I have a problem with a VBA script. I guess the solution is simple, but I just wasnt able to figure it out....

So basically, I have a workbook that contains many worksheets. Each worksheet contains the exact same format of a table (same number of rows and columns). What I wanted to do was to create a new worksheet and in that worksheet, have averages of all those values.

So for example in cell B2 I want to have average of cells B2 from all the other worksheets. I therefore created a macro that does this, this is not a problem, the problem is however, that in that macro, all sheets are referred to by their names and since I have many of these workbooks with differently named sheets, this would not work. I therefore tried to change the name of the first sheet to actual reference of sheet - i.e. Sheet(1) and the last one as Sheet(x) - where x is the number of sheets I calculated previously. Unfortunately the code doesnt work, could anyone please suggest to me how to modify this so that it works properly? I am only copying the problematic part of the code:

x = Sheets.Count
Sheets.Add After:=Sheets(x)
Range("B2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheets(1):Sheets(x)!RC)"

Upvotes: 2

Views: 126

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Try this. You are not offseting your sheet names (in fact, you're not even using the sheet names. In the context of a formula, Sheets(1) is meaningless. You need to use Sheets(1).Name and offset it appropriately:

"=AVERAGE('" & Sheets(1).Name & ":" & Sheets(x).Name & "'!RC)"

Upvotes: 2

Related Questions