• 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...

  • 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.

Gimmers

New Member
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
 

Attachments

Marc L

Excel Ninja
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 …
 

Gimmers

New Member
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.
 

Marc L

Excel Ninja
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) ?
 

Gimmers

New Member
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.
 

Marc L

Excel Ninja
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 …
 

Gimmers

New Member
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.
 

Marc L

Excel Ninja
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 …
 

Gimmers

New Member
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
 

Marc L

Excel Ninja
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 …
 

Marc L

Excel Ninja
First, if this demo result is not smart, you already know why …

Paste this code to the Sheet10 module :​
Code:
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 !
 

Marc L

Excel Ninja
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:
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

New Member
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.
 

Marc L

Excel Ninja

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 …)
 

Gimmers

New Member
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.
 

Marc L

Excel Ninja
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 …
 

Gimmers

New Member
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.
 

Marc L

Excel Ninja
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
i'm aware of what a table and it's purpose is
but in fact you are not and have to learn …

So what else ?! :rolleyes:
 

Gimmers

New Member
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.
 

Marc L

Excel Ninja
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) !
 

Gimmers

New Member
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/madrocketscientist.com/jerrybeaucaires-excelassistant/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.
 

Marc L

Excel Ninja
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 …
 
Top