Combining two tables into one master table

sjgerrard

New Member
Joined
Nov 3, 2016
Messages
2
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Ruca13

Board Regular
Joined
Oct 13, 2016
Messages
84
If you don't want to copy the headers, i would sugest:
Code:
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

New Member
Joined
Nov 3, 2016
Messages
2
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

Board Regular
Joined
Oct 13, 2016
Messages
84
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:

Code:
[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:

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top