How to copy the data from multiple excel worksheets to a master sheet on same workbook

[cooldude]

Hello all,

I am struggling to find a solution to my problem which is as below:

I have a excel workbook with name - sales.xlsx which has 3 worksheets - online, shop and master.

Currently we enter the data manually to worksheets online and shop. We want a macro which can automatically copy over the data to the master sheet from both - shop and sales sheet whenever any data is entered to either of the two sheets.

It will a really great help if someone can let us know the solution for this please.

Thanks a lot.

 

[krrishkrsna]

below is my understanding
1. entering Shop details in sheet Shop
2. entering Online details in sheet Online
3. u want to add sheet Shop and Online data one below another
4. refresh automatically whenever new data added


if above understanding is right then follow below steps
1. add one more column at last as Shopping Method and below that update sheet name either Online or Shop
2. convert your data to Table of both sheet (Online and Shop) and give proper table name
3. select sheet Shop
4. click on menu Power Query > From Table/Range > select column Date > click menu Transform > click on Data Type > select Date
5. click on menu Home > click on Close & Load drop down arrow > select Click & Load To > select Only Create Connection and click on button OK
6. select sheet Online
7. repeat the steps 4 & 5
8. click on menu Power Query > click on Append > select option Two Tables > select Shop table name & then Online table name
9. click OK
10. give meaningful name for this table below the Name box of Query Settings on right side of Query Editor window
11. click on menu Home > click on Close & Load drop down arrow > select Click & Load. this will create new sheet with appended data
12. select any cell in append table > click menu Design > click Properties in External Table Data tab > click on properties icon in popup window which is next to Name box (below the connection label)
13. in tab Usage > enable the check box Refresh every xx > click OK > click OK


NOTE if you dont know how to install Power Query add-ins, search for in youtube and install it. its free and good package
Power Query is available for Excel 2010 or above

 

[oldbrewer]

If it were me I would only have one single worksheet

col A would be shop or online

I will make an example sheet and post in a couple of minutes

 

[oldbrewer]

[TABLE="width: 984"]
<colgroup><col><col><col span="3"><col><col span="9"></colgroup><tbody>[TR]
[TD]where[/TD]
[TD]date[/TD]
[TD]amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]shop[/TD]
[TD]online[/TD]
[TD]total[/TD]
[TD][/TD]
[TD][/TD]
[TD]row 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]66[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]238[/TD]
[TD="align: right"]330[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]382[/TD]
[TD="align: right"]657[/TD]
[TD="align: right"]1039[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/10/2017[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]525[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]67[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]78[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]09/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]87[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula giving 92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]63[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]109[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]=SUMPRODUCT(($A$2:$A$28=G$5)*($B$2:$B$28=$F6)*($C$2:$C$28))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]02/10/2017[/TD]
[TD="align: right"]108[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]03/10/2017[/TD]
[TD="align: right"]107[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]03/10/2017[/TD]
[TD="align: right"]106[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shop[/TD]
[TD="align: right"]03/10/2017[/TD]
[TD="align: right"]105[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]03/10/2017[/TD]
[TD="align: right"]104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]online[/TD]
[TD="align: right"]03/10/2017[/TD]
[TD="align: right"]103[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]