1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merge data from multiple spreadsheets into one master sheet.

Discussion in 'VBA Macros' started by Gimmers, Jun 18, 2018.

  1. Gimmers

    Gimmers New Member

    Messages:
    13
    Hi,

    I’m after some assistance creating the correct VBA to do the following:


    · There will be several workbooks stored in the same location (with different filenames but they should start with ‘Chase updates’) C:\Users\SmithJ\Documents\Spreadsheets

    · In these workbooks is a sheet called. ‘Chases’ all workbooks in the folder and the master sheet have the same format.

    · I want to copy the data from the chase spreadsheets the data I want to copy will start in Cell B14 and got across to cell I14 but the amount of rows of data will vary from sheet to sheet.

    · Once copied I want the data entered into the master spreadsheet ’Chase updates for WC’ on the ‘Totals’ sheet. Again the data would be pasted into the same location as it was pasted from B14 toI14 down, the data would need to merge all the data from the spreadsheets into this master sheet rather than copying over the data each time.



    I know there are similar solutions on the internet but I have not been abel to modify them to suit what I require.


    i have also included examples of both sheets attached

    Attached Files:

  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    Hi !

    It seems that's a bad use of tables !
    As a table grows up automatically when data are added to it
    so why your tables have so many blank rows ?!
    So according to your attachment, first row copied will be pasted to
    Total!B307 cell ! If it's not you are expecting
    so clean workbooks without any blank row in any table.
    And maybe you ever do not need data as table …

    You can start your own code just activating the Macro Recorder
    and operating manually …
  3. Gimmers

    Gimmers New Member

    Messages:
    13
    we use one master spreadsheet that has the tables in and at the moment we copy and paste the data from the chase updates sheets into the master.

    i'm not 100% sure what you mean by clean workbooks but the sheets are filled in weekly.

    the data copied should be pasted into the same range int my master workbook.

    sorry if i have misunderstood.
  4. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    In the result workbook you use a table but why this table has
    so many blank rows ?‼ Select cell B13 : table starts in row #13,
    ends in row #306 but rows #14 to 306 are blanks ‼ Why ?
    Read in Excel help what is a table and its purpose …
    Do you really need data as table (as an Excel feature) ?
  5. Gimmers

    Gimmers New Member

    Messages:
    13
    because thats where i want the data from the chase updates sheets to be pasted into so that i can update the graphs on the other sheets, i'm aware of what a table and it's purpose is and yes i need it as a table bu if i didnt would that change was i would be asked for i would still want the data to be copied into the specific range.
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    A code for a common range does not look like to a code
    for any table Excel inner feature ! So if working with tables
    according to your attachment, as your result table has more than
    300 blank rows (a no sense for who knows Excel and a table !),
    your first pasted row will be in row #307
    leaving in place all previous blank rows …
  7. Gimmers

    Gimmers New Member

    Messages:
    13
    right OK, thank you i wasn't aware the code would be different if i was pasting into a table, this is where my lack of ability with VBA comes in

    at the moment i have to manually copy the data from around 60 spreadsheets into the table on the master spreadsheet which takes me around an hour and a half and i was just looking for a way to make the process quicker.
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    It would be quick & at beginner level if result workbook has no table
    just a header row …
    In this case, delete all blank rows in the table ('cause of formulas)
    then convert the table to a range, save the workbook
    and attach this clean workbook …

    To avoid same issue with source data workbooks
    - 'cause of same Dumb or Dumber blank rows formulas design ! -
    the copy must use some trick instead of the table range …

    As no issue with tables only with smart workbooks …
  9. Gimmers

    Gimmers New Member

    Messages:
    13
    i could but i don't see how that would solve my problem i would still have to manually copy and paste the data in which was the thing i wanted to avoid in the first place. thank you anyway
  10. Marc L

    Marc L Excel Ninja

    Messages:
    4,258


    No as if it were the case I didn't ask you to attach a clean workbook ! …​
  11. Gimmers

    Gimmers New Member

    Messages:
    13
    i do not understand what you mean
  12. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    That was at child level …

    Last try via other way : attach at least a couple of source data workbooks
    and a result workbook filled - so no empty ! - according to
    both source data workbooks …
  13. Gimmers

    Gimmers New Member

    Messages:
    13
    Hello, i've attached 2 source files with data and the master with the data input into there also.

    Attached Files:

  14. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    First, if this demo result is not smart, you already know why …

    Paste this code to the Sheet10 module :​
    Code (vb):
    Sub Demo4SmartTables()
         Dim F$
             If Me.ListObjects.Count = 0 Then Beep: Exit Sub
             Application.ScreenUpdating = False
             Me.Activate:  Me.ListObjects(1).Range(1).Select
             F = Dir(ThisWorkbook.Path & "\Source Chase Updates*.xlsx")
    Do Until F = ""
      With GetObject(ThisWorkbook.Path & "\" & F)
        If Evaluate("ISREF('[" & F & "]Chases'!A1)") Then
          With .Worksheets("Chases")
            If .ListObjects.Count Then .ListObjects(1).DataBodyRange.Copy Me.ListObjects(1).InsertRowRange
          End With
        End If
            .Close False
             F = Dir
      End With
    Loop
             Application.ScreenUpdating = True
    End Sub
    You may Like it !
    Thomas Kuriakose likes this.
  15. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    After running next demo according to your last attachment design,
    as the Excel real table aspect is not respected in your workbooks,
    so the data are pasted like any ordinary range
    but you could have some rows outside the result table.
    In this case, you'll have to rearrange it …

    Paste this code to the Sheet10 module :​
    Code (vb):
    Sub Demo0()
         Dim R&, F$
             Application.ScreenUpdating = False
             R = 14
             F = Dir(ThisWorkbook.Path & "\Source Chase Updates*.xlsx")
    Do Until F = ""
      With GetObject(ThisWorkbook.Path & "\" & F)
        If Evaluate("ISREF('[" & F & "]Chases'!A1)") Then
            With .Worksheets("Chases")
                With .Range("B14", .[B13].End(xlDown)).Resize(, 8)
                     .Cells.Copy Cells(R, 2)
                      R = R + .Rows.Count
                End With
            End With
        End If
            .Close False
             F = Dir
      End With
    Loop
             Application.ScreenUpdating = True
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    Gimmers and Thomas Kuriakose like this.
  16. Gimmers

    Gimmers New Member

    Messages:
    13
    That works. Thank you so much!!!!

    The only other question i have is on the source sheets in column B14 down the name is determined by a formula - =IF($B$3="","",$B$3)

    Is there anyway of copying this value over, not the formula but the name it shows. it works where it has been typed in manually but not when using the formula.
  17. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    This is the classic issue when any attachment reflects the real workbook !
    Which demo you use ?
    Other point : each time the procedure is launched,
    the result table has to be cleared ? (Erasing previous data …)
  18. Gimmers

    Gimmers New Member

    Messages:
    13
    The formula's were in the sample workbooks i sent across. the second one you posted worked like a charm, the one i inserted into the Sheet10 module.

    i know but this will be a weekly thing so the 'Master' would be saved with a new filename hence the end of the name being WC.
  19. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    Maybe but none of data in attachment uses any formula !

    So the master initial table created whatever by Dumb or Dumber
    can be cleared ? It can avoid the issue described in post #15
    as second demo is just a bad tables workaround …
  20. Gimmers

    Gimmers New Member

    Messages:
    13
    i need the table if that's what you mean. listen i appreciate your help and that you've taken your own time to look at this for me but at this stage it might be best to just leave it i especially do not like being referred to as 'Dumb' the reason people come to this forum is to get help if we all know VBA excel etc we wouldn't need to come to forums like this.
  21. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    If you well read I never wrote to delete this table ‼
    Just to clear / clean it as it should be just before loading any data
    in order to avoid issue as per poor initial design !

    How is any empty table when you create it ?
    Just a header row and an insert row …

    I couldn't imagine you was the author 'cause you wrote
    but in fact you are not and have to learn …

    So what else ?! :rolleyes:
  22. Gimmers

    Gimmers New Member

    Messages:
    13
    i never said i was the author of the sheet, and i couldn't see any data in the sample spreadsheet i initially upload and still cannot now. i also won't be insulted when all i am trying to do is ask for some help, you might consider being slightly less condescending next time you reply to someones post.
  23. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    So much ado about nothing 'cause you are not the author,
    so why such a bad reaction as you are not the author
    of these tables but in the need ? … :rolleyes:

    Maybe I'm - a bit - rough but it's for avoiding issue I already met.
    I hope next time you will better explain in your initial post
    with an attachment accordingly …

    I'm still expecting a clear answer to choose between both ways :
    - the Dumb way where an issue may occur,
    - the smart way correcting the master table to avoid the issue
    (as any Excel user can do if he knows what is an Excel table).

    Just notice than both like gas factory codes can be avoided
    as you already have the first demo working since more than
    a decade with tables (smart ones) !
  24. Gimmers

    Gimmers New Member

    Messages:
    13
    Doesn't matter who the author is

    i included an attachment in my original post and tried to make it as clear as a i could, perhaps understand that due to my lack of ability with Excel / VBA i may not be asking in the most efficient way or using the correct terminology but i tried to explain it as best i could. as i said i have found a solution that i was able to modify from:

    https://sites.google.com/a/madrocke.../merge-functions/consolidate-wbs-to-one-sheet

    i do not know how to correct the table to avoid the issue nor do i know what a gas factory code is. or what a decade has to do with anything.
  25. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    Like my second demo, your link is just copying
    any classic cells range keeping formulas as well … :rolleyes:

    As several times yet explained, a table does not need any blank row
    and your 300 blank ones is a bit a lot and very superfluous
    loosing all the benefits of using a table …

    Another way should use the inner filter of the tables but
    what a surprise your source tables don't have any filter on my side,
    your master table yes but I need on source ones !

    It's the first time I meet such tables things
    among tens of thousands of threads …

Share This Page