hope someone could help -Macro to assist averaging data set's over multiple worksheets

[Fringedweller]

I am hoping that someone can help me with a macro to average data from multiple time points
Simply i have a 4 columns Day, Date, Time, Data
The same format appears on each worksheet with in the workbook which i need sort and Average over the data range

The data sort should be by date (ascending) then by Time (Ascending)
Time is based on a 24 Hour period data points can arrive at any point during this period

I would like to add that i have tried to do it my self using the inbuilt sort functions but can no seem to get it to work across multiple sheets
also i got lost in the Pivot table grouping options

Cell range on all worksheets is Range("A2:D500") Includes headers A2-D2
I guess the output columns would be F2:G500 F=Time(whole Hour, example 1am, 2am, 3am ..etc) g= averaged data from previous time until current time

Then i can Graph the averages of a week based on date and hourly intervals for each worksheeet
my goal is then combine all worksheets into a single graph using each worksheets title as labels

example of data
[TABLE="width: 205"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Day[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"] Time[/TD]
[TD="align: center"]Data[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]23:37:29[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]23:27:46[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20:45:09[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20:41:54[/TD]
[TD="align: center"]1.7[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20:38:39[/TD]
[TD="align: center"]2.3[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20:35:24[/TD]
[TD="align: center"]1.9[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]18:21:51[/TD]
[TD="align: center"]1.4[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]12:32:57[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Sat[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]12:29:42[/TD]
[TD="align: center"]1.4[/TD]
[/TR]
[TR]
[TD="align: center"]Fri[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]21:14:29[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Fri[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]17:20:28[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Fri[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]17:17:14[/TD]
[TD="align: center"]1.3[/TD]
[/TR]
[TR]
[TD="align: center"]Fri[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]17:13:57[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Fri[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]5:39:40[/TD]
[TD="align: center"]1.3[/TD]
[/TR]
[TR]
[TD="align: center"]Thu[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]4:09:20[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Wed[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]15:50:40[/TD]
[TD="align: center"]1.3[/TD]
[/TR]
[TR]
[TD="align: center"]Wed[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]13:21:03[/TD]
[TD="align: center"]1.3[/TD]
[/TR]
[TR]
[TD="align: center"]Wed[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0:19:00[/TD]
[TD="align: center"]1.4[/TD]
[/TR]
[TR]
[TD="align: center"]Wed[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0:15:49[/TD]
[TD="align: center"]1.5[/TD]
[/TR]
[TR]
[TD="align: center"]Wed[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0:12:38[/TD]
[TD="align: center"]1.3[/TD]
[/TR]
[TR]
[TD="align: center"]Mon[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20:32:29[/TD]
[TD="align: center"]1.4[/TD]
[/TR]
[TR]
[TD="align: center"]Mon[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]6:41:32[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Mon[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]6:38:19[/TD]
[TD="align: center"]1.3[/TD]
[/TR]
[TR]
[TD="align: center"]Sun[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]14:12:30[/TD]
[TD="align: center"]1.2[/TD]
[/TR]
[TR]
[TD="align: center"]Sun[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]14:09:15[/TD]
[TD="align: center"]1.4[/TD]
[/TR]
</tbody>[/TABLE]
I hope i have given enough information for someone to assist..

Cheers... any other questions please ask

 

[Fringedweller]

My Current Macro

Sub Datapoints()


' Import Files from C:\Raw Data
Dim strFile As String
Dim WS As Worksheet
strFile = Dir("C:\Raw Data\*.csv")
Do While strFile <> vbNullString
Set WS = Sheets.Add
With WS.QueryTables.Add(Connection:= _
"TEXT;" & "C:\Raw Data" & strFile, Destination:=Range("$A$1"))
.Name = strFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
strFile = Dir
Loop


' Delete not needed cells on all worksheets
Dim a As Long
For a = 1 To Sheets.Count
Worksheets(a).Range("B:B").ClearContents
Worksheets(a).Range("D:N").ClearContents
Next a
MsgBox "Complete"
On Error Resume Next
For Each WS In ThisWorkbook.Worksheets
WS.Name = Left(WS.Cells(1, 1).Value, 31)
Next
On Error GoTo 0
For Each WS In ThisWorkbook.Worksheets


' Increase all data values by 1 to ensure all values are >1
WS.Activate
Range("D3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0.1,RC[-1]+1,"""")"
Selection.AutoFill Destination:=Range("D3:D1000"), Type:=xlFillDefault
Range("D3:D1000").Select
WS.Activate


' Copy & Paste Data field as text intead of formula
Columns("D:D").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:E").Select
Selection.ClearContents

' extract contents - Day, Date and Time from Rows
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True
Range("E2").Select
ActiveCell.FormulaR1C1 = "Time"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Data"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Day"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Date"
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("A:D").Select
Columns("A:D").EntireColumn.AutoFit

' Format Columns
Columns("A:A").Select
Selection.NumberFormat = "@"
Columns("B:B").Select
Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
Columns("B:B").Select
Selection.NumberFormat = "[$-409]d-mmm;@"
Columns("C:C").Select
Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
Columns("D:D").Select
Selection.NumberFormat = "0.00"

' Bold Fields Headers
Range("A2:D2").Select
Selection.Font.Bold = True
Range("A1").Select
Selection.Font.Bold = True
Sheets("Main Sheet").Select
Range("A1:T1").Select
Selection.ClearContents
Next
End Sub




'" Ok this is where I have a lot of trouble,
'" I need an Hourly Average for the data contained in "Data"(column D) on each worksheet over the Data Range (Date and time)
'" Example data between - 1am : 2am will be displayed as an average at 2am
'" Maybe using column "F" showing hourly intervals starting F3 and using column "G" to show associated averaged data for that time period


'" The Averages will then be graphed in chronological order on each worksheet over the range period (Number of Days)
'" Finally this average data from all worksheets will be consolidated to create Main Graph on Worksheet "Main Sheet"
'" I now hope that you can see what I am trying to achieve