I am just starting VBA, and I know I have a lot of reading to do, but I need to get something working for work asap.
I have a workbook with ~100 sheets and I would like to create a summary sheet with one row for each sheet, and in each row, 12 cells for the averages of 12 ranges that sheet. For example, the sheet "Spain" has B100:B110 for January data, C100:C110 for February data, D100:D110 for March, etc. On the summary sheet, I would like one row to be Spain then the January average, February average, etc. Here is my first sorry attempt: (I know there are more efficient ways to go, but as I kept getting errors I tried to get around things by brute force)
This is not doing anything at all. Thanks in advance for any help.
I have a workbook with ~100 sheets and I would like to create a summary sheet with one row for each sheet, and in each row, 12 cells for the averages of 12 ranges that sheet. For example, the sheet "Spain" has B100:B110 for January data, C100:C110 for February data, D100:D110 for March, etc. On the summary sheet, I would like one row to be Spain then the January average, February average, etc. Here is my first sorry attempt: (I know there are more efficient ways to go, but as I kept getting errors I tried to get around things by brute force)
Code:
Sub average() Dim sh As Worksheet, N As Long
Dim i As Long, M As Long
Dim janavg As Long
Dim febavg As Long
Dim maravg As Long
Dim apravg As Long
Dim mayavg As Long
Dim junavg As Long
Dim julavg As Long
Dim augavg As Long
Dim sepavg As Long
Dim octavg As Long
Dim novavg As Long
Dim decavg As Long
N = Sheets.Count - 1
M = 2
For i = 1 To N
Worksheets(i).Activate
janavg = Application.WorksheetFunction.average(Range("B109:B118"))
febavg = Application.WorksheetFunction.average(Range("c109:c118"))
maravg = Application.WorksheetFunction.average(Range("d109:d118"))
apravg = Application.WorksheetFunction.average(Range("e109:e118"))
mayavg = Application.WorksheetFunction.average(Range("f109:f118"))
junavg = Application.WorksheetFunction.average(Range("g109:g118"))
julavg = Application.WorksheetFunction.average(Range("h109:h118"))
augavg = Application.WorksheetFunction.average(Range("i109:i118"))
sepavg = Application.WorksheetFunction.average(Range("j109:j118"))
octavg = Application.WorksheetFunction.average(Range("k109:k118"))
novavg = Application.WorksheetFunction.average(Range("l109:l118"))
decavg = Application.WorksheetFunction.average(Range("m109:m118"))
Sheets("Summary").Range("B" & M).PasteSpecial (janavg)
Sheets("Summary").Range("C" & M).PasteSpecial (febavg)
Sheets("Summary").Range("D" & M).PasteSpecial (maravg)
Sheets("Summary").Range("E" & M).PasteSpecial (apravg)
Sheets("Summary").Range("F" & M).PasteSpecial (mayavg)
Sheets("Summary").Range("G" & M).PasteSpecial (junavg)
Sheets("Summary").Range("H" & M).PasteSpecial (julavg)
Sheets("Summary").Range("I" & M).PasteSpecial (augavg)
Sheets("Summary").Range("J" & M).PasteSpecial (sepavg)
Sheets("Summary").Range("K" & M).PasteSpecial (octavg)
Sheets("Summary").Range("L" & M).PasteSpecial (novavg)
Sheets("Summary").Range("M" & M).PasteSpecial (decavg)
M = M + 1
Next i
End Sub