sungirl2215
New Member
- Joined
- Jul 14, 2022
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
New to VBA and working on a new tool. I have a string of subs that are run by one master sub.
- Copy data table from sheet "Step 2" to a new sheet called "Averages"
- Add a row under the header row of the table in "A5"
- Bold the text that will go into the new row created starting at "A5"
- 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??
VBA Code:
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