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]

where	date	amount
shop	01/10/2017	25				col G
online	01/10/2017	33
online	01/10/2017	39
shop	01/10/2017	55				shop	online	total			row 5
online	01/10/2017	66			01/10/2017	92	238	330
online	01/10/2017	77			02/10/2017	382	657	1039
shop	01/10/2017	12			03/10/2017	105	420	525
online	01/10/2017	23			04/10/2017	0	0	0
online	02/10/2017	34			05/10/2017	0	0	0
shop	02/10/2017	45			06/10/2017	0	0	0
online	02/10/2017	56			07/10/2017	0	0	0
online	02/10/2017	67			08/10/2017	0	0	0
shop	02/10/2017	78			09/10/2017	0	0	0
online	02/10/2017	89			10/10/2017	0	0	0
online	02/10/2017	98
shop	02/10/2017	87
online	02/10/2017	76
online	02/10/2017	65
shop	02/10/2017	64				formula giving 92
online	02/10/2017	63
online	02/10/2017	109			=SUMPRODUCT(($A$2:$A$28=G$5)*($B$2:$B$28=$F6)*($C$2:$C$28))
shop	02/10/2017	108
online	03/10/2017	107
online	03/10/2017	106
shop	03/10/2017	105
online	03/10/2017	104
online	03/10/2017	103

<colgroup><col><col><col span="3"><col><col span="9"></colgroup><tbody>
</tbody>