Auto sort consolidation book

Hi all

I have a master workbook which runs a macro/ VBA code every 5 mins or so and captures/consolidates call entries (data) tracked in individual excel workbook files.

The Macro works fine but I am faced with one problem

The data is nothing but calls tracked by different team members , when the macro runs it first captures data from say team member "Caren" then from team member "Anne" and its here that problem lies since I wanted to look at this consolidated data in a sorted format ( first by date and then by time)

Ideally this can be done manually but when we look at the fact that the macro runs every 5 mins its not feasible to manually sort data every 5 mins.

Is it possible to add a VBscript so that whenever the macro runs it also automatically sort the data (First by Date and then by Time)

Enclosing sample of the master file ( has been modified to protect confidentiality )

Thanks in advance

Due to server issues with VBA express I am unable to upload the sample file - I'll do so shortly

Atlast I have been able to upload the sample in zip format ( excel file size is around 1.2 mb)

Yes, this should be rather straightforward to implement. HOWEVER it would help if you were to specify which of your worksheets you want to order. Is it 'Combined Tracker'?

Hi Rolf

This is how it works

Ideally every team member has an individual tracker ( workbook) and that tracker has 4 sheets ( this can increase if more office is included)

Now as you can see - these 4 sheet data ( eg London tracker \ Montana tracker ) are captured and consolidated in the respective tracker in the master file ( in the sample uploaded I have only shown data in one tracker\sheet)

Ideally all the sheet will have similar capture - there fore sorting needs to happen in all the 4 sheets .

Now coming to the consolidation tracker ( the first sheet in master tracker)- here I want the data to be consolidated from the 2\3\4\5 sheets (London\Liverpool sheets) - so that this section show me the collective call flow that's happening

I presume - I have clarified the requirement - if not please feel to revert

enclosing the sample of the individuals tracker


PS: one important thing - since I may have more team member what we have done is defined the location of individual tracker - than assign name - I presume the temp file wont cause any issues while capturing ( that is if an excel file is open then there would also be a temp file present there )

It remains unclear to me why you wouldn't just sort the consolidated worksheet...

Please explain.

I hope that'll be my last question, but won't promise.

Haha Rolf

The reason I want to sort all the sheets ( London/Liverpool etc) is because when I look into the master file - I need to see the how different offices are working plus I need a consolidated angle as well (on overall how my team has functioned till then).

For example - when I look into London sheet - some of the things that I need to look at is how many calls have landed for that office / What were the latest calls that came in \ how my people are handling the calls there - in a real time scenario ( in this case every 5 mins since the script runs every 5 mins to capture and consolidate the data from individual team member workbooks)

Thinking about your requirements a bit longer it occurred to me that the easiest solution might be to place the following SheetActivate event handler into the ThisWorkbook VBA module of your workbook:
[vba]
Const TOP_OF_LEFTMOST_COLUMN As String = "A6"
Const TOP_OF_RIGHTMOST_COLUMN As String = "S6"
Const TOP_OF_DATE_COLUMN As String = "D6"
Const TOP_OF_TIME_COLUMN As String = "C6"

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Code Sheet" And Sh.Name <> "Analysis Sheet" Then SortSheet
End Sub
Sub SortSheet()
Range(TOP_OF_LEFTMOST_COLUMN & ":" & TOP_OF_RIGHTMOST_COLUMN).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort _
Key1:=Range(TOP_OF_DATE_COLUMN), Order1:=xlDescending, _
Key2:=Range(TOP_OF_TIME_COLUMN), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub
[/vba]
This code assumes that all your tracking worksheets have the same format. It will cause the ordering to take place whenever you switch worksheets. That might be all you want and it is easier to implement than to place code into your macro. I have to admit though that I have no idea what will happen if you were to change worksheets in the middle of the execution of your macro. If that should be a problem you could declare a global boolean variable, e.g. bMacroRunning, that you set to True when your macro launches and to False when your macro finishes and then make the call to SortSheet conditional, e.g. by inserting the statement

[vba]
If bMacroRunning Then Exit Sub
[/vba]

cool let me test it out Rolf