Consulting

Results 1 to 8 of 8

Thread: Auto sort consolidation book

  1. #1
    VBAX Regular
    Joined
    Aug 2008
    Posts
    46
    Location

    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

  2. #2
    VBAX Regular
    Joined
    Aug 2008
    Posts
    46
    Location
    Atlast I have been able to upload the sample in zip format ( excel file size is around 1.2 mb)

  3. #3
    VBAX Regular
    Joined
    Oct 2009
    Location
    Fremont, CA
    Posts
    72
    Location

    Please clarify

    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'?

  4. #4
    VBAX Regular
    Joined
    Aug 2008
    Posts
    46
    Location
    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 )

  5. #5
    VBAX Regular
    Joined
    Oct 2009
    Location
    Fremont, CA
    Posts
    72
    Location

    Still one more question

    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.

  6. #6
    VBAX Regular
    Joined
    Aug 2008
    Posts
    46
    Location
    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)

  7. #7
    VBAX Regular
    Joined
    Oct 2009
    Location
    Fremont, CA
    Posts
    72
    Location

    Give this a try

    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]
    Hope this helped,
    Rolf Jaeger
    SoarentComputing
    Software Central

  8. #8
    VBAX Regular
    Joined
    Aug 2008
    Posts
    46
    Location
    cool let me test it out Rolf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •