Combining two tables into one master table

[sjgerrard]

Hi there

I am currently in the process of trying to set up a master table based upon two tables I already have and to formulate a code that will allow any new data added to the two tables to copy into the master table. All tables are in the same workbook and are set out as follows:

• Sheet one = "Master Tracker"
• Sheet two = "Outgoing"
• Sheet three = "Incoming"

I have found the following code that works when the data is in its worksheet form (not as tables).

Sub Summarize()​

Dim ws As Worksheet​

Dim lastRng As Range​

Application.ScreenUpdating = False ' speed up code​

ThisWorkbook.Sheets("Master Tracker").Range("A2:M65536").ClearContents 'clear​

For Each ws In ThisWorkbook.Worksheets​

Set lastRng = ThisWorkbook.Sheets("Master Tracker").Range("a65536").End(xlUp).Offset(1, 0)​

Select Case ws.Name​

Case "Master Tracker" 'exlude​

'do nothing​

Case "Folha1"​

Case Else​

ws.Activate​

'copy data from individual sheets​

Range("A250", Range("X65536").End(xlUp)).Copy lastRng​

End Select​

Next​

Application.CutCopyMode = False 'clear clipboard​

Application.ScreenUpdating = True​

Sheets("Master Tracker").Activate​

Cells.Select​

End Sub​

The only issue with this code is that it copies across the headers from the "Outgoing" and "Incoming" sheets which I would like it to exclude as they are the same headers as already stated on the "master tracker".

Is there a code similar to this that could be used for tables (the ideal situation!)? Otherwise how do I go about excluding copy of the headers on both the "Outgoing" and "Incoming" worksheets in this code.

Another aspect that would be super helpful would be if the code could auto-run (another ideal situation).

Any help would be much appreciated. I know zero to nothing about VBA so have only got this far thanks to forum's as such and playing with codes people have added.

Please note I am using excel 2007.

Thanks,
Sarah

 

[Ruca13]

If you don't want to copy the headers, i would sugest:

Range (cells(2,1), cells(2,1).end(xldown).end(xlright)).select

This will select everything apart from row 1. Translating to "not-code", you select cell A2, and then do a ctrl+shift+ down arrow, and then ctrl+shift+ right arrow.

About the automatic start, inside the worksheet (not the module) of each table, change on the top box on the right to "change", and then call the macro.

This means that every time you change something in the Outgoing or the Incoming worksheet, the macro would run.

Please see this link for more detail:

http://www.mrexcel.com/forum/excel-...event-directly-visual-basic-applications.html

 

[sjgerrard]

Hi Ruca13

Thanks so much for your response.

Where in my code would I place the this? I have tried it in various places and also tried replacing parts and it consistently comes back with the need to debug.

Also I am very new to vba (i.e. am having to teach myself as I go) so I have no clue how to call the macro. I have figured out how to get to the box inside the worksheet but every code I try doesn't seem to work. Could you please clarify a bit further. If it helps at all the macro is under "Module1".

Thanks,
Sarah

 

[Ruca13]

Hi Sarah,

Unfortunately I'm also a beginner so I cannot explain things with the detail needed for a full understanding.

I would suggest substitute this line:

[I]Range("A250", Range("X65536").End(xlUp)).Copy lastRng[/I]

About calling the macro, as you have module 1, slight above you should have Sheet1 (name of the sheet), sheet2 (name of the sheet, Thisbook (i hope this is the translation).

Inside the sheets of Outgoing and Incoming, write the exact same code:

Private Sub Worksheet_Change(ByVal Target As Range)


Call Macro1 ()


End Sub

Where Macro1 is the name of your macro.

If I'm correct, whenever you change something in Outgoing or Incoming, the macro will run, and so the Master will update.

I hope this helps.

 

[arthurbr]

Lots of examples at Copy/Paste/Merge examples.
Perhaps this helps