I have 25 excel files generated daily that I would like to copy a few cells out into one main one. I have had a fiddle and have attempted some excel macros to copy what I require although am coming unstuck when trying to grab a particular value as the cell will never be static.
I require to record the peak usage of 25 systems ( column c ) as well as the total duration this hits its highest peak ( column D ). The layout of the each file is the same although some may have more lines than others and have duplicate entries in column c. This is why I have tried to group each MAX although this maximum number changes daily and I can have multiples of them. How do I take the max number and total duration this occurs when the cell that the total is displayed in can change daily ( i.e one day i will have 1 22 in column C, the next i could have 5 listed in which case the total i want wont always be C7) is this some kind of multiple array? or can this be done in vbscript?
After I have grabbed this data I just need to paste it into a "central" file on a new row for each day for each of the 25 files i grab data from.
Does anyone know how to get around the moving total or suggest another method? regards in advance.
Macro below I have attempted which gets the data and sorts the figure I require as well as a couple of example files i grab the data from.
' GetData Macro
' Get spreadsheet data.
' SortTheData Macro
ActiveWorkbook.Worksheets("CH").Sort.SortFields.Add Key:=Range("C6:C60"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("CH").Sort.SortFields.Add Key:=Range("B6:B60"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
' SubTotals Macro
ActiveCell.FormulaR1C1 = "Duration"
ActiveCell.FormulaR1C1 = "=VALUE(RC[-3])"
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True