Need help with VBA Macro to output range calculations to new sheet

[thecraigstone]

Hello gents,

I'm an IT project coordinator and I'm in need of a custom Excel macro. The self-help videos on YouTube and articles on Google aren't cutting it.

I have not worked with VBA before, but I need to create this as a macro because the sheet is dynamic.

The current sheet captures, once an hour, the load time of site and site content by location (in milliseconds). Send/receive load times are captured in multiple columns (download 1mb, 5mb, etc - upload 1mb, 5mb, etc), as well as ping at time of capture.

What I need is a macro that [when run] outputs Daily average of each column value (displayed in seconds), by location, printed to a new sheet (together on a sheet).

Columns are:

• Location
• Time (mm/dd/yyyy hh:ss)
• Load site (time in ms)
• Site content (time in ms)
• Download 1mb(ms)
• Download 5mb(ms)
• Download 10mb(ms)
• Upload 1mb(ms)
• Upload 5mb(ms)
• Upload 10mb(ms)
• Ping (ms)

I can do this manually using formulas on the new sheet and dragging down for the respective ranges, but need a faster way to do this. Can anyone help?

 

[thecraigstone]

When I use Macro Recorder to record the setup of the pivot table via macro and try to run it I get the following error:

Sub DailyAveragesBySite()
'
' DailyAveragesBySite Macro
' Creates a pivot table on a new sheet with daily averages of response times sorted by site.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Columns("A:K").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R1048576C11", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Location")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Load Site"), "Count of Load Site", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Site Content"), "Count of Site Content", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Download 1MB"), "Count of Download 1MB", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Download 5MB"), "Count of Download 5MB", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Download 10MB"), "Count of Download 10MB", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Upload 1MB"), "Count of Upload 1MB", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Upload 5MB"), "Count of Upload 5MB", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Upload 10MB"), "Count of Upload 10MB", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Ping"), "Count of Ping", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Site Content" _
)
.Caption = "Average of Site Content"
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Download 1MB" _
)
.Caption = "Average of Download 1MB"
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Download 5MB" _
)
.Caption = "Average of Download 5MB"
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Download 10MB")
.Caption = "Average of Download 10MB"
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Upload 1MB")
.Caption = "Average of Upload 1MB"
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Upload 5MB")
.Caption = "Average of Upload 5MB"
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Ping")
.Caption = "Average of Ping"
.Function = xlAverage
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Average of Ping"), "Average of Ping", xlAverage
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Upload 10MB")
.Caption = "Average of Upload 10MB"
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Load Site")
.Caption = "Average of Load Site"
.Function = xlAverage
End With
Range("A4").Select
Selection.Group Start:=True, End:=True, By:=1, Periods:=Array(False, _
False, False, True, False, False, False)
Columns("B:J").Select
Selection.NumberFormat = "0.00"
Range("B9").Select
End Sub

 

[thecraigstone]

Figured it out, thanks.