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

 

[afc171]

Yes, that is working now thanks.
I removed 2 items from StockRoom, ran the sub again but it only updated the Product (A) column.
If StockRoom sheets gets updated is it easier to just clear CopyData and run Sub FindingLastRow again? Also if there are more StockRoom sheets how would I add these to the last empty row in CopyData?

 

[mrshl9898]

You may as well just run it all when needed. It won't take long to copy things around.

Are there specific sheets to copy? eg:

Sub FindingLastRow()

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

Set master = Sheets("CopyData")

''added a bit to clear out CopyData

MLastRow = master.Cells(master.Rows.Count, "A").End(xlUp).Row
master.Range("A3:I" & MLastRow).ClearContents


''then this will do A sheet, so copy and repeat, changing the sheet name

Set sht = Sheets("StockRoom")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
MLastRow = master.Cells(master.Rows.Count, "A").End(xlUp).Row
sht.Range("A3:I" & LastRow).Copy master.Range("A" & MLastRow + 1)

''end of copy section


End Sub

Or is it all sheets Except CopyData?

Sub FindingLastRowAllOtherSheets()

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

Set master = Sheets("CopyData")

''added a bit to clear out CopyData
MLastRow = master.Cells(master.Rows.Count, "A").End(xlUp).Row
master.Range("A3:I" & MLastRow).ClearContents

For Each sht In ThisWorkbook.Worksheets
    If sht.Name <> "CopyData" Then
        LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
        MLastRow = master.Cells(master.Rows.Count, "A").End(xlUp).Row
        sht.Range("A3:I" & LastRow).Copy master.Range("A" & MLastRow + 1)
    End If
Next sht
''end of copy section

End Sub

 

[afc171]

Yes there are specific sheets.
I tried your 2nd code but as there are 2 hidden sheets it was pulling the info from them too, is there a way to exclude them? If not no worrys the first code seems to work well and i can add certain sheets to pull info from.

 

[mrshl9898]

Should be

If sht.Name <> "CopyData" and sht.Name <> "otherone"  and sht.Name <> "another" Then

*I always mess this up. If and doesn't work, try or

 

[afc171]

that works! lol i was being a numpty

Thanks for your help buddy works a treat now

 

[afc171]

Slight issue...
I have noticed that after a certain amount of copied rows that some formulas don't seem to work which vlookup from a hidden sheet. Weird thing is that the formula is in the cells but I get #Value! error even though the cells above with the numbers have the same formula and copied from the same sheet

 

[mrshl9898]

What's the formula in the working and non working cells? Also is pasting as values an option?

 

[afc171]

Book1
	A	B	C	D	E	F
1	product	warehouse	long_description	bin_number	physical_qty	on_order_qty
2	09063	LC	DELETED - YELLOW SELF SEAL BIOHAZARD BAG	LC4	0	0
3	100 VIAL BOX	LC	Clear100 Box for 0.5-2ml Vials		13	0
4	1001-8910	LC	500ML PP CENTRIFUGE	LC20	1	5
5	1004-4870	LC	60ML NALGENE BOTTLES	LC4	2	0
6	1005-4870	LC	125ML NALGENE BOTTLES	LC19	9	0
7	1008-0264	LC	DELETED - 5ML PLASTIPAK SYRINGES	LC	0	0
8	1012-7400	LC	5ML PIPETTES INIVIDUAL WRAP	LC12	4	0
9	102000	LC	DELETED - 3ML GLASS VIALS	LC	0	0
10	102010	LC	3ML AMBER GLASS VIALS	LC	0	0
11	102040	LC	DELETED - 11ML GLASS VIALS	LC	0	0
12	102058	LC	Polyflux 17L Dialysis Cartridges 1 x 24		0	0
13	1188-9610	LC	DELETED - NITRILE GLOVES MEDIUM	LC2	0	0
14	1189-9610	LC	DELETED - NITRILE PURPLE GLOVES (LRG)	LC2	0	0
15	12004271	LC	ZE5 Additive	LC16	0	1
16	12004272	LC	Cytometer Cleaner	LC21	3	0
17	12004403	LC	ZE Series QC Bead, 5ml, Pack of 3	LC21	2	0
18	1203-9817	LC	2ML PLASTIPAK SYRINGES	LC10	0	0
19	1204-4717	LC	20ML PLASTIC SYRINGES	LC10	0	0
20	1231-9149	LC	5ML PLASTIPAK SYRINGES	LC10	0	0
21	1234-0479	LC	DELETED -STERILIZATION POUCHES 10 X 25CM	LC	0	0
22	1235-0489	LC	DELETED - AUTOCLAVE TAPE	LC	0	0
23	1236-2439	LC	DELETED - SHARPS CONTAINER 7LITRES	LOFT	0	0
24	1237-8657	LC	DELETED - HAND CREAM	LC14	0	0
25	1240-2330	LC	DELETED - PAPER TOWEL ROLL	LC	0	0
26	1245-7896	LC	MILLIPORE STERICUP VACUM FILTER UNIT	LC13	0	0
27	1260-4617	LC	EPPENDORF 2.5 ML COMBI TIPS	LC9	2	0
28	1262-4597	LC	50ML EPPENDORF COMBITIP PLUS	LC20	2	0
29	1265-1406	LC	50ML PLASTIPAK SYRINGES	LC10	0	0
30	1265-7636	LC	EPPENDORF 10ML COMBI TIP	LC9	2	0
31	1269-4587	LC	EPPENDORF 5 ML COMBI TIPS	LC9	9	14
32	1270-9005	LC	DELETED - STERILAZATION POUCHES 10x15cm	LC	0	0
33	1275-4296	LC	DELETED - PAPER TOWEL ROLL	LC15	0	0
34	1275-9005	LC	DELETED - STERILI POUCHES (20x27cm)	LC14	0	0
35	1278-9005	LC	DELETED -STERILIZATION POUCHES (30X51cm)	LC	0	0
36	1279-9985	LC	DELETED - 7ML PLASTIC BIJOUS	LC4	0	0
37	1292-2850	LC	DELETED - 250ML DIAMOND WEIGH	LC10	0	0
38	1299-2840	LC	DELETED - 5ML DIAMOND WEIGH BOAT	LC10	0	0
39	1351114	LC	VIVAFIX 398/550	LCLAB201	1	0
40	1351118	LC	VIVAFIX 649/660	LCLAB201	2	0
41	1351303	LC	DAPI	ELAB20	0	0
42	1450016	LC	TC10/TC20 COUNTING SLIDES	ELABRT	0	0
43	1450022	LC	TRYPAN BLUE	ELABRT	0	0
44	1514-6686	LC	DELETED - DISINFECTANT WIPES	LC14 COSH	0	0
45	151515	LC	POLY BOX 150X150X150MM (LV012) AND OUTER	LC/P8	0	0
46	1525-3837	LC	3ML PASTEUR PIPETTE	LC7	16	7
47	1560-1944	LC	DELETED - AUTOCLAVE TAPE	LC4	0	0
48	1564-1954	LC	DELETED - STERILAZATION POUCHES 10x15cm	LC17	0	0
49	1564-9795	LC	DELETED - ALCOHOL WIPES	LC14 COSH	0	0
50	1565-1954	LC	DELETED -STERILIZATION POUCHES 10 X 25CM	LC13	0	0
51	1573-0679	LC	DELETED - STERI POUCH 13.5 X 26CM	LC13	0	0
52	1578-0319	LC	MILLIPORE STERICUP VACUM FILTER UNIT	LC15	0	0
53	1581-7531	LC	SARTOLAB P FILTERS	LC11	0	0
54	15879152	LC	10ML PLASTIC SYRINGES	LC10	3	0
55	1610393	LC	PRECISION ALL BLUE STD (PK 5)	LCLAB201	2	0
56	1610396	LC	UNSTAINED PROTEIN STANDARDS (5PK)	LCLAB201	2	0
57	1610610	LC	DITHIOTHREITOL (DTT)	LCLAB28	2	0
58	1610737	LC	LAEMMLI SAMPLE BUFFER	LC16	3	0
59	1610747	LC	LAEMMLI SAMPLE BUFFER (4x)	LC16	3	0
60	1610772	LC	TGS (10x)	LC17	4	0
61	1704157	LC	PVDF TRANSBLOT TRANSFER PACK	LCLAB28	5	0
62	1704159	LC	MIDI NITROCELLULOSE TURBO TRANSFER PACK	LCLAB28	12	0
63	1704275	LC	TRANS-BLOT LF PVDF TRANSFER KIT	ELABRT	0	0
64	1705061	LC	CLARITY WESTERN ECL SUBSTRATE, 500ML	LC16	5	0
65	1710-0803	LC	PAPER LAB COATS - LARGE	LOFT	0	0
66	1710-0804	LC	DELETED PAPER LAB COATS - EXTRA LARGE	LOFT	0	0
67	1710-0805	LC	PAPER LAB COATS - EXTRA EXTRA LARGE	LOFT	0	0
68	1710-0915	LC	DELETED - SHOE COVERS	LC26	0	0
69	18072-D	LC	DELETED - SARTOLAB P FILTERS	LC11	0	0
70	20X30	LC	20X30 BAGS 250g	LOFT	3	0
71	262626	LC	CL17-05 PELI POLY BOX 26X26X26 AND OUTER	LC/P8	0	0
72	292926	LC	MED DRY ICE BOX 283X283X259MM	LC/P8	0	0
73	292926INNER	LC	MEDIUM DRY BOX 29X29X26 INNER ONLY	LC/P8	0	0
74	300180	LC	15.5MM WADLESS CAPS	LC	0	0
75	300203	LC	22MM WADLESS CAPS	LC	0	0
76	302323	LC	DOUBLE WALLED STOCK BOX 305X228X228MM	LC/P8	0	0
77	303030	LC	PELI POLY BOX 300X3000X300MM AND OUTER	LC/P8	0	0
78	30389256	LC	5ML TIPS	LC9	3	0
79	30389279	LC	TIPS 50-1000UL TRAD	LC7	2	0
80	30389283	LC	RAININ STD REFILL TIPS	LC7	5	0
81	30389287	LC	TIPS 2-250UL TRAD SPACE SAVER	LC7	4	0
82	30389291	LC	TIPS 0.1-20UL SPACESAVER PK960 LTS	LC7	8	21
83	30389292	LC	TIPS 50-1000UL SPACESAVER PK768 LTS	LC7	17	26
84	30389299	LC	TIPS 2-200UL SPACESAVER PK960 LTS	LC7	13	15
85	30389303	LC	TIPS 50-1000UL SPACESAVER PK768 LTS	LC7	0	0
86	312313	LC	MEDIUM SINGLE WALLED BOX 305X228X130MM	LC/P8	0	0
87	313131	LC	DOUBLE WALLED STOCK BOX 305X305X305MM	LC/P8	0	0
88	342800-0020	LC	DELETED - 2ML CLEAR VIALS (0331380B)	LC	0	0
89	342805-0020	LC	2ML AMBER VIALS (339027)	LC	0	0
90	342820-0116	LC	DELETED - BLUE LIDS (0331381G)	LC	0	0
91	342820-1110	LC	NATURAL/TEAL CLOSURE	LC2	12	0
92	342825-0110	LC	DELETED - AMBER LIDS (33909)	LC	0	0
93	342825-0116	LC	DELETED - AMBER/BLUE LIDS (033903)	LC	0	0
94	352008	LC	BD 12 X 75MM TUBES	LC16	18	28
95	362800-0005	LC	0.5ML NATURAL VIALS	LC1	24	28
96	362800-0020	LC	2ML NATURAL VIALS	LC1	11	0
97	362805-0005	LC	0.5ML AMBER VIALS (339028)	LC3	8	0
98	362805-0020	LC	2ML AMBER VIALS	LC1	6	0
99	362820-0110	LC	NATURAL CLOSURE	LC3	24	28
100	362820-0116	LC	NATURAL CLOSURE WITH BLUE INSERT	LC3	0	0
101	362825-0111	LC	AMBER CLOSURE	LC3	0	0
102	362825-0112	LC	AMBER CLOSURE WITH YELLOW INSERT	LC3	0	0
103	362825-0115	LC	AMBER CLOSURE WITH RED INSERT	LC3	0	0
104	362825-0116	LC	AMBER CLOSURE WITH BLUE INSERT	LC3	0	0
Stk_Qty-LC

Book1
	A	B	C	D	E	F	G	H	I
2	Flow Tubes	352008	1 Box	1 Box	2 Boxes	18	LC16
3	2 x Laemmli *	1610737	1 Bottle	0 Bottles	1 Bottle	#N/A	#N/A
4	4 x Laemmli *	1610747	1 Bottle	0 Bottles	1 Bottle	#N/A	#N/A
5	10 x TGS running buffer *	1610772	1 Box	0 Boxes	1 Box	#N/A	#N/A
6	CRITERION 4-15% TGX STAIN-FREE GEL	5678084	Storage in the cold room	15 gels	Replenish stock to 25 gels	#N/A	#N/A
7	ANY KD CRITERION TGX STAIN-FREE GEL	5678124	Storage in the cold room	15 gels	Replenish stock to 25 gels	#N/A	#N/A
8	LTS L10 refill tips	30389291	1 Tower	1 Tower	1 Tower	#N/A	#N/A
9	LTS L1000 refill tips	30389292	1 Tower	2 Towers	3 Towers	#N/A	#N/A
10	LTS L200 refill tips	30389299	1 Tower	1 Tower	1 Tower	#N/A	#N/A
11	5 ml stripette	1012-7400	1 Bag	2 Bags (½ Box)	2 Boxes	4	LC12
12	50 ml falcon tubes	1033-4131	1 Tray	12 Trays	1 Box of 500	#N/A	#N/A
13	5 ml Gilson tips	1035-7852	1 Bag	0	1 Box	#N/A	#N/A
14	15 ml falcon tubes	1057-9691	1 Tray	5 Trays	2 Boxs of 500	#N/A	#N/A
15	50 ml stripettes	1063-6391	1 Bag	2 Bags (½ Box)	1 Boxes	#N/A	#N/A
16	1.8 ml cryovials	1067-4511	1 Box	0	1 Box	#N/A	#N/A
17	10 ml stripette	1067-7341	1 Bag	2 Bags (½ Box)	1 Box	#N/A	#N/A
labstock

Cell Formulas
Range	Formula
F2:F17	F2	=VLOOKUP([@product],'Stk_Qty-LC'!A:F,5,FALSE)
G2:G17	G2	=VLOOKUP([@product],'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$104	F2:G17

 

[afc171]

Just made a temp sheet with some data to test but as you can see on the labsheet cell F3 is showing N/A with the same formula as F2

 

[mrshl9898]

Strange it gets a couple of results. What's the result on the below?

='labstock'!B2='Stk-Qty-LC'!A58