Automatically updating Master sheet if other sheets are updated?

[afc171]

Hi guys,
I have a spreadsheet with a "Master" sheet which has all information listed from all the other sheets. At the moment I have used = to add data from other sheets into the Master one.
Now someone might add or remove a line from any of the sheets which you then have to manually re-do the master sheet.
Is there a different method to update the master file automatically if any data is changed in other sheets? each sheet has the same headers in cells A:I

Thank you

 

[mrshl9898]

Hi,

Some examples would be helpful, data and formulas in use, could be a few solutions.

 

[afc171]

LC.xlsm
	A	B	C	D	E	F	G	H	I
2	Product	Product code	Old product code	Stock Unit	Book out quantity	Available stock	Bin Number	Quantity	Depart.
3	BIOHIT 5ML TIPS (BULK PK)	1148085	250	CAMLAB-CAM002	Bag of 250 (1)	0	LC23	0	0
4	LTS refill tips-filter-boxed RT-L5000 NEW STYLE	30389256	192	ANACHEM-ANA001	Box of 192 (1)	3	LC9	0	0
5	Traditional refill tips-stacked – GPS1000 NEW STYLE	30389279	768	ANACHEM-ANA001	Stack of 768 (1)	2	LC7	0	0
6	Traditional refill tips-stacked GPS-10G NEW STYLE	30389283	960	ANACHEM-ANA001	Stack of 960 (1)	5	LC7	0	0
7	Traditional refill tips-stacked GPS-250 NEW STYLE	30389287	960	ANACHEM-ANA001	Stack of 960 (1)	4	LC7	0	0
8	LTS refill tips-stacked – GPS-L10 NEW STYLE	30389291	960	ANACHEM-ANA001	Stack of 960 (1)	8	LC7	0	0
9	LTS refill tips-stacked – GPS-L1000 NEW STYLE	30389292	768	ANACHEM-ANA001	Stack of 768 (1)	17	LC7	0	0
10	LTS refill tips-stacked – GPS-L250 NEW STYLE	30389299	960	ANACHEM-ANA001	Stack of 960 (1)	13	LC7	0	0
11	LTS refill tips-stacked GPS-L300 NEW STYLE	30389303	960	ANACHEM-ANA001	Stack of 960 (1)	0	LC7	0	0
12	10ml pipette individual wrap	1067-7341	200	FISHER-FIS001	Box of 200 (1)	8	LC12	0	0
13	50ml pipette individual wrap	1063-6391	100	FISHER-FIS001	Box of 10 (1)	6	LC10	0	0
14	50ml clear glass vial	IVM1C20050C	90	ORIGIN-LAB003	Tray of 90 (1)	4	LC4	0	0
15	500ml centrifuge tube	1001-8910	36	FISHER-FIS001	Box of 36 (1)	1	LC20	0	0
16	60ml Nalgene bottle	1004-4870	12	FISHER-FIS001	Pack of 12 (1)	2	LC4	0	0
17	125ml Nalgene bottle	1005-4870	12	FISHER-FIS001	Pack of 12 (1)	9	LC19	0	0
CopyData

Cell Formulas
Range	Formula
A3:A17	A3	=Table12[@Product]
B3:B17	B3	=Table12[@[Product code]]
C3:C17	C3	=Table12[@[Box size]]
D3:D17	D3	=Table12[@Supplier]
E3:E17	E3	=Table12[@[Pack size]]
F3:F17	F3	=Table12[@[Available stock ]]
G3:G17	G3	=Table12[@[Bin Number]]
H3:H17	H3	=Table12[@Quantity]
I3:I17	I3	=Table12[@Department]

 

[afc171]

LC.xlsm
	A	B	C	D	E	F	G	H	I
1	Product	Product code	Removal quantity from entry room	Re-order point	Quantity to order	Available stock	Bin Number	QUANTITY	Department
2	Flow Tubes	352008	1 Box	1 Box	2 Boxes	18	LC16
3	2 x Laemmli *	1610737	1 Bottle	0 Bottles	1 Bottle	3	LC16
4	4 x Laemmli *	1610747	1 Bottle	0 Bottles	1 Bottle	3	LC16
5	10 x TGS running buffer *	1610772	1 Box	0 Boxes	1 Box	4	LC17
6	CRITERION 4-15% TGX STAIN-FREE GEL	5678084	Storage in the cold room	15 gels	Replenish stock to 25 gels	6	LCLAB28
7	ANY KD CRITERION TGX STAIN-FREE GEL	5678124	Storage in the cold room	15 gels	Replenish stock to 25 gels	102	LCLAB28
8	10 ml syringes	15879152	1 Box	0	1 Box	3	LC10
9	LTS L10 refill tips	30389291	1 Tower	1 Tower	1 Tower	8	LC7
10	LTS L1000 refill tips	30389292	1 Tower	2 Towers	3 Towers	17	LC7
11	LTS L200 refill tips	30389299	1 Tower	1 Tower	1 Tower	13	LC7
12	5 ml stripette	1012-7400	1 Bag	2 Bags (½ Box)	2 Boxes	4	LC12
13	50 ml falcon tubes	1033-4131	1 Tray	12 Trays	1 Box of 500	1	LC20
14	5 ml Gilson tips	1035-7852	1 Bag	0	1 Box	1	LC17
15	ELISA plates	1054-4522	Individual	10 plates	1 Box	0	LC6
16	15 ml falcon tubes	1057-9691	1 Tray	5 Trays	2 Boxs of 500	10	LC21
17	50 ml stripettes	1063-6391	1 Bag	2 Bags (½ Box)	1 Boxes	6	LC10
StockRoom

Cell Formulas
Range	Formula
F2:F17	F2	=VLOOKUP([@[Product code]]&"",'Stk_Qty-LC'!A:F,5,FALSE)
G2:G17	G2	=VLOOKUP([@[Product code]]&"",'Stk_Qty-LC'!A:F,4,FALSE)

Named Ranges
Name	Refers To	Cells
'Stk_Qty-LC'!Query_from_CRSS	='Stk_Qty-LC'!$A$1:$F$550	F2:G17

 

[afc171]

Lets say someone adds or removes something from the StockRoom sheet (or other sheets) how can I update the CopyData sheet automatically or with a VBA?

 

[mrshl9898]

I am a bit lost here, CopyData refers to Table12 but that doesn't look to be StockRoom.

I don't tend to use tables or named ranges much, let's try something else.

I see no rows are named, do you just need code to copy down CopyData rows to be the same amount of rows as the StockRoom?

 

[afc171]

I am a bit lost here, CopyData refers to Table12 but that doesn't look to be StockRoom.

I don't tend to use tables or named ranges much, let's try something else.

I see no rows are named, do you just need code to copy down CopyData rows to be the same amount of rows as the StockRoom?

Sorry I removed some sheets to make it abit simple when pasting here.

Yes just need the row data from StockRoom or other sheets copied into CopyData

 

[mrshl9898]

In these other sheets, are they new columns or new rows in the master?

You need to find the last row in the copy from sheet and copy the formulas down to there it seems, at least for the first sheet.

Assuming there may be more or less rows, clear the data first apart from the top formula row. Something like:

Sub FindingLastRow()

Dim sht As Worksheet
Dim master As Worksheet
Dim LastRow As Long

Set sht = Sheets("StockRoom")
Set master = Sheets("CopyData")

master.Range("A4:A1000").ClearContents
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
master.Range("A3:I3").Copy master.Range("A3:I" & LastRow)

End Sub

 

[afc171]

for some reason not copying to CopyData

 

[mrshl9898]

Is it meant to copy it over to CopyData from StockRoom?

Sub FindingLastRow()

Dim sht As Worksheet
Dim master As Worksheet
Dim LastRow As Long

Set sht = Sheets("StockRoom")
Set master = Sheets("CopyData")

master.Range("A4:A1000").ClearContents
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
sht.Range("A3:I" & LastRow).Copy master.Range("A3:I" & LastRow)

End Sub