Calculate average for each column and display it in a blank row at the top. Rows and Columns are dynamic based on data users copy into workbook.

[sungirl2215]

New to VBA and working on a new tool. I have a string of subs that are run by one master sub.

1. Copy data table from sheet "Step 2" to a new sheet called "Averages"
2. Add a row under the header row of the table in "A5"
3. Bold the text that will go into the new row created starting at "A5"
4. Find the average of all data in column A, posting result in cell "A5" > loop until there is more data in the next columns 5th cell **here is where I'm stuck** It all works great until looping, but I only get averages for column A. What part of this am I missing??

Sub RunStep4Macros()
CopyData
NewRow
BoldRow
AverageColumn
End Sub
 
Sub CopyData()
'Copy data to a new sheet and rename new sheet to Averages
Set ws = Sheets("Step 2")
 
ws.Copy After:=Sheets("Step 2")
 
Set wsNew = Sheets(Sheets("Step 2").Index + 1)
 
wsNew.Name = "Averages"
End Sub

Sub NewRow()
'Insert a new row at the top of the data
Range("A5").EntireRow.Insert

End Sub

Sub BoldRow()
'Bold Row 5 for averages
 Range("5:5").Font.Bold = True
 
End Sub

Sub AverageColumn()
'declare a variable
Do

Dim ws As Worksheet
Set ws = Worksheets("Averages")

'apply the formula to average all numbers in column A
ws.Range("A5") = Application.WorksheetFunction.Average(ws.Range("A:A"))

Loop Until IsEmpty(ActiveCell.Offset(1, 1))

End Sub

 

[Alex Blakenburg]

Without a visual of how your data looks at the start and then at the end, I am guessing a little.
In the below I am assuming that by the time it calls the Average Sub
• your heading row is 4
• you want the average to appear at row 5
• your data which by virtue of having had a row inserted now starts at row 6

Sub AverageColumn()
    'declare a variable
    Dim ws As Worksheet, rngAvg As Range
    Dim lastRow As Long, hdgRow As Long, firstRow As Long, avgRow As Long
    Dim lastCol As Long
    Dim iCol As Long
    Set ws = Worksheets("Averages")
    
    hdgRow = 4
    avgRow = hdgRow + 1
    firstRow = avgRow + 1
    
    With ws
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(avgRow - 1, Columns.Count).End(xlToLeft).Column
    
        For iCol = 1 To lastCol
            Set rngAvg = .Range(.Cells(firstRow, iCol), .Cells(lastRow, iCol))
            .Cells(avgRow, iCol).Value = Application.Average(rngAvg)
        Next iCol
    End With

End Sub