Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2013
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macro for fetching data from dynamic range of a spreadsheet to another spreadsheet.

    hey all, this is my very first post on this forum and what ever I have learned, Its from windows secret only. I am greatful to all the community members for helping the others. In my case, I have 84 members in my team and at the end of the day, each of them has to report their part of work. So for that, I have created 84 different macro enabled sheet for each of the associate in which they will have to paste the data and hit submit button in the sheet. That will upload their part of work to a master consolidated sheet automatically, and also the duplicacy was also taken care of in case of hitting the submit button twice.

    Every spread sheet has three tables on sheet1, say Table one is X Table two is Y and Table three is z. Now table X's, Table Y's and Table Z's no. of column remains constant (A:G), (A:M) and (A:I) respectively, but the no. of rows in case of each associate differs. At the end of the day, when each of the associate submits his/her part of work, its my responsibility to copy and paste from each of the table form each sheet to three different sheets, obviously with the names of table, sheet 1 = TableX, Sheet 2=Table Y and sheet 3=Table z. In the beginning, when my team was small, it was good, but as team kept growing, it was getting on my nerves to consolidate all that data every day. So for that, I created 84 different macro enabled spreadsheets for each of the associate in which they will have to paste the data and hit submit button in the sheet. That will upload their part of work to a master consolidated sheet automatically, and also the duplicacy was also taken care of in case of hitting the submit button twice. As the no. of rows were dynamic in nature, I had to set the range to (A2:G21), (A26:M45) and (A49:I64) excluding the headings of each table respectively in below mentioned macro :

    Sub One_Click_Submit()
    Dim AssociateWS As Worksheet
    Dim NewPosition As Long
    Dim ConsolidatedXL As Object
    Dim AssociateXL As Object
    Dim z As Long
    Set AssociateXL = GetObject("D:\Daily work\Tracker_A.XLSm") 'Tracker_A is for associate A, the value chages per associate's spreadsheet
    Set AssociateWS = AssociateXL.Worksheets("Sheet1")
    Set ConsolidatedXL = GetObject("E:\Daily Work\July\Consolidated.XLSx")
    AssociateWS.Range("A2:G21").Select 'setting range and copying the data
    Selection.Copy
    ConsolidatedXL.Activate
    Worksheets("Table X").Activate
    endrow = Range("A65536").End(xlUp).Row
    Cells(endrow + 1, 1).Select
    ActiveSheet.Paste ' Pasting data
    With CreateObject("Scripting.Dictionary") ' For Checking the duplicacy
    For z = [A1].End(xlDown).Row To 1 Step -1
    If .Exists(Cells(z, "A") & Cells(z, "B")) Then Rows(z).Delete
    .Item(Cells(z, "A") & Cells(z, "B")) = "whatever"
    Next
    End With
    AssociateXL.Activate
    AssociateWS.Range("A26:M45").Select
    Selection.Copy
    ConsolidatedXL.Activate
    Worksheets("Table Y").Activate
    endrow = Range("A65536").End(xlUp).Row
    Cells(endrow + 1, 1).Select
    ActiveSheet.Paste
    With CreateObject("Scripting.Dictionary")
    For z = [A1].End(xlDown).Row To 1 Step -1
    If .Exists(Cells(z, "A") & Cells(z, "B")) Then Rows(z).Delete
    .Item(Cells(z, "A") & Cells(z, "B")) = "whatever"
    Next
    End With
    AssociateXL.Activate
    AssociateWS.Range("A49:I64").Select
    Selection.Copy
    ConsolidatedXL.Activate
    Worksheets("Table Z").Activate
    endrow = Range("A65536").End(xlUp).Row
    Cells(endrow + 1, 1).Select
    ActiveSheet.Paste
    With CreateObject("Scripting.Dictionary")
    For z = [A1].End(xlDown).Row To 1 Step -1
    If .Exists(Cells(z, "A") & Cells(z, "B")) Then Rows(z).Delete
    .Item(Cells(z, "A") & Cells(z, "B")) = "whatever"
    Next
    End With
    ConsolidatedXL.Save
    Set ConsolidatedXL = Nothing
    Set AssociateWS = Nothing

    Beep
    MsgBox "Thank you for submitting the Data."
    Beep

    End Sub

    Its working fine but the problem is it totally depends upon clicking the submit button. Sometimes, the associates forgot to hit the submit button and thus his/her part was not included in Consolidated sheet.I need help to create a macro for the master consolidation sheet that will enable me to fetch all the data by clicking the fetch button; from all the spreadsheets in the same way, three separate sheets for three separate tables. Yes, the opposite of the above mentioned macro. The major problem i am having is how to define the range for each table, that is obviously dynamic in nature and is on the the same sheet. Please help
    Last edited by Mohit; 2013-07-25 at 05:27.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Mohit

    Welcome to the Lounge!
    Now, your post is quite detailed, so I need to look at it carefully before responding.
    But, generally speaking, I think you are right to consider 'pulling' the data from each of your Associate's files into a Master file, rather than have your Associate's 'push' their data into the Master.
    The reasons are that when you 'pull' the data from each of their files, you access their file in Read-Only mode and so can 'pull' their latest 'saved' data even if they currently have the file open and in use.
    If each Associate has to 'push' the data into a Master file, then the Master file cannot be in use by anyone else while this is done.

    I'll work on a proper reply and get back to you.

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    Mohit (2013-07-25)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Mohit

    Need to confirm a few details from you:
    For your three tables X, Y and Z, on sheet1 of the Associate data files, are the 'table headers' in the same row for each table for ALL associates?
    For example,
    Table X always starts with Table X header in row 1 (data in rows 2 up to row 21)
    Table Y always starts with Table Y header in row 25 (data in rows 26 up to row 45)
    Table Z always starts with Table Z header in row 48 (data in rows 49 up to row 64)

    Are all the Associate data files in the same network folder location?

    zeddy

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Mohit (2013-07-25)

  6. #4
    New Lounger
    Join Date
    Jul 2013
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Yes. You are correct. The headers remains constant in rows 1, 25 and 48. I was thinking that instead of having 84 different spreadsheets, can I fetch data from a single spreadsheet containg 84 different dedicated sheets, one for each associate (say A1, A2, A3, A4 and so on) where everything remains the same (the tables and all). Is it possible? Will it increase the data corruption chances while fetching? will it slow down the file opeing time for both the sheets? Thanks for your help Zeddy.

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Mohit

    It is NOT a good idea to have a single source datafile with 84 sheets, one each for your associates!

    One reason is that how would the 84 users manage to update this single file???? Using a 'Shared' workbook will bring you enormous headaches and risk of data corruption. Also, what would happen when you needed to add another five associates, for example.

    The best way is definitely to have separate datafiles, one for each Asscociate, and use a simple filenaming convention for their datafiles e.g. "Tracker-J.Smith.xlsb", "Tracker-A.Jones.xlsb" etc

    Now, you could set up a simple system whereby a merge tool would process ALL datafiles held in a specified folder. This would be OK if you could rely on there being only one file per associate in the folder. If they made a copy of their file, then any tool that processed ALL files in a folder would then double-count these etc. Yes, you could add tests in your merge process to detect such duplicates, but this would slow the process down and add complication.

    My recommendation is to have a specified list of Associates , and use a merge tool to process each person in the List. You could easily add new persons etc. This eliminates the need to check for duplicate imports etc (provided your Team List didn't have the same person in twice of course!).

    I will send an example merge tool which does this, in my next post.

    zeddy

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Mohit

    OK, I have created a sample Merge Tool which shows how to merge data from up to 100 datafiles into a Consolidation File, using the structure of your files.
    I have tested it with 24 sample data files (it took 4 seconds to merge 24 datafiles on my laptop).

    For Excel2007 and later, I find it most efficient to use the .xlsb file format rather than .xlsm and .xlsx file formats.
    The .xlsb file format is typically half the file size, which means it is so much faster to load and work with on networks.
    The .xlsb file format also allows macros to be included.
    I recommend you switch to using this more efficient file format for all Excel work where possible.

    In my Merge Tool, I have included named cells which allow you to specify where data is located.
    A 'clicker' is also provided to set the destination folder for the Consolidation file.
    You will need to amend the vba code to make use of these, but it is a simple task. Ask if you need further help with this.

    For the purposes of this example, I have assumed that everthing will be placed into a folder named C:\TEST\Daily Work\

    Using the zip file:
    First, create a folder on you C:\ drive named
    C:\TEST
    Then create a subfolder named Daily Work within this folder, i.e.
    C:\TEST\Daily Work\
    Then Extract all files from the zip file into C:\TEST\Daily Work\

    The attached zip file contain the Master processing Tool for merging the datafiles:
    [rzMergeTool-v1.00.xlsb]

    There is a blank template file used for the merged data from the datafiles:
    [BlankConsolidated.xlsb]

    To create a new datafile for any User, simply copy a blank template datafile and re-name it with the name of the Associate, for example:
    [Tracker-A.Blank.xlsb] >> [Tracker-F.Bloggs.xlsb] etc

    The zip file contains 24 sample datafiles, corresponding to sample names entered into the Team List in the merge Tool.

    Open the file [rzMergeTool-v1.00.xlsb] with macros enabled.
    Click the button labelled [click here to Merge..] and then watch as the status cells are updated as the datafile is merged.

    You should be able to adapt the blank files to match your setup.
    I have commented the vba code to explain what is going on at every step.
    This should enable you to adapt this for your needs.

    Any questions, please ask..

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2013-07-28 at 12:32. Reason: typo

  9. The Following User Says Thank You to zeddy For This Useful Post:

    mnlaguerta (2014-04-21)

  10. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Mohit

    ..if you don't want to use .xlsb file formats, you can use .xlsm for the [rzMergeTool-v1.00] and .xlsx for the datafiles.
    You would just need to change the formula for the Data File in column [G] of the Team List from ..&".xlsb" to ..&".xlsx"

    Or, you could do everything in .xls file format etc etc.

    zeddy

Tags for this Thread

Posting Permissions

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