VBA Check if value in Sheet1 exists in Sheets(2-40), then copy found values from sheet 1 to sheet(2-40)

[CobusVanWyk]

Hi Everyone

I have tried to find a solution in the already asked questions section but to no avail... I need some help please. I will explain to the best of my ability. I'm new to VBA.

I am trying to write a VBA code to perform the mentioned task in the title. Here is some information:

I have created a workbook containing our stock codes in the warehouse in sheet 1. So it contains all stock items with a specified code in Column C of sheet 1. It also contains quantities and pricing info etc.
In sheets 2 to 40 in the same workbook, are our companies products. Certain products use certain stock items, but not all. Each product has its own sheet.
Each product sheet (2-40) therefore only contains some of the stock codes found in sheet 1.
I want the code, to look at the stock codes found in the product sheets (sheet 2 - 40). The product codes are also in column C.
If the code is present in Sheet2-40 respectively, I want the values found from sheet 1 (column D,E,J,K) to be copied in sheet 2-40 (Column D,E,F,H) respectively.

Notes:
- The function needs to compare all text codes found in column C2 downwards in sheets 2-40 to column C2 downwards in sheet1, in the same workbook.
- Column C is a stock code but in the same row as the stock code, are various other values of which I only want to copy over certain values in the other sheets, if found.
- The function needs to loop from column C row 2 (row 1 are headers) to the last row in the column that contains a value (in Sheet1).
- Column C will always be used for the check between Sheet1 and Sheet2-40 to see if a matching value exists.

Goal:
- Loop from column/row C2 downwards to the last row that contains a value in sheets 2-40 to see if these values exist in Column/row C2 downwards of Sheet 1.
- If a matching code is found on Sheet2-40 respectively, populate column values D,E,J,K from sheet 1 to column values D,E,F,H in matching row in sheet 2-40. The rows may differ in sheets 2-40.

I apologize if this is unclear, it is hard to convey the information properly without attaching a sample workbook.
I am attaching a mini sheet for Sheet 1 and a product Sheet X

Mini Sheet: Sheet 1

Alti Product and Pricing sheet - COBUS TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Type	Supplier	StockCode	Description	Reorder Pt (Min)	QtyOnHand	QtyOnSalesOrder	QtyOnPO	Available	UnitPriceUSD	UnitPriceZAR
2	ASCEND	(ZAR) RF DESIGN CC	AS-AN001	Cable Assembly (Taisync) SMA-F RA BLK/H TO MMCX-M, RG316/U, 200mm(CA20FRABK25MDM2)	1,00	2	1	0	1		R 115,56
3	ASCEND	(ZAR) RF DESIGN CC	AS-AN002	Cable Assembly (Taisync) SMA-F RA BLK/H to MMCX-M, RG316/U, 400mm(CA20FRABK25MDM4)	1,00	2	1	0	1		R 120,19
4	ASCEND	(USD) SOUTH WEST ANTENNAS	AS-AN003	Omni Antenna, Half Wave Dipole 2.1 - 2.5 GHz 2.15 dBI (1001-048)	2,00	2	2	0	0	$ 115,00
5	ASCEND	(ZAR) RF DESIGN CC	AS-AN004	Ascend Taoglas 868 MHz Flex Circuit Antenna, 220mm, MCX-M (FXP280.07.0220M)..	2,00	2	0	0	2		R 102,98
6	ASCEND	(ZAR) RF DESIGN CC	AS-AN005	Ascend Taoglas 915 MHz Flex Circuit Antenna, 220mm, MCX-M ..	2,00	0	0	0	0		R 102,98
7	ASCEND	(USD) Taisync Technology LLC	AS-AP001	2.4G, Eth-in-Eth-out Module with USB2UART adaptor - Airside	1,00	1	1	0	0	$ 550,00
8	ASCEND	(USD) SHENZHEN CAINAN TECHNOLOGY CO., LTD	AS-AP002	Alti CF Taisync Mount - Airside	1,00	0	0	0	0	$ 0,00
9	ASCEND	(ZAR) The Aviation Shop	AS-AP003	Remove before Flight (PS35)		-1	0	0	-1		R 0,00
10	ASCEND	(USD) SHENZHEN GREPOW BATTERY CO., LTD	AS-BA26325C	LiPo 2600mAh 3S 25C Gens Ace	2,00	0	0	0	0	$ 19,49
11	ASCEND	(USD) SHENZHEN GREPOW BATTERY CO., LTD	AS-BA33445C	Lipo 3300mAh 4S 45C..	4,00	8	0	0	8	$ 24,92
12	ASCEND	(ZAR) INTERNAL ALTI R&D	AS-BA352	Lipo 3500mAh 2S RX GensAce..	2,00	12	1	0	11		R 249,18
13	ASCEND	(ZAR) JBS ENERGY SOLUTIONS	AS-BA523	Build 11.1V Li-ion 3S2P 5.6Ah Battery (Datalink)..	2,00	0	1	1	0		R 895,00
14	ASCEND	(ZAR) REBUL	AS-BX001	Ascend Freight Crate	1,00	1	0	0	1		R 1 550,00
15	ASCEND	(USD) CASTLE	AS-CN001F	Connector Castle Creations 6.5mm Female	1,00	-1	1	0	-2	$ 2,65
16	ASCEND	(USD) CASTLE	AS-CN001M	Connector Castle Creations 6.5mm Male	1,00	-1	1	0	-2	$ 2,65
17	ASCEND	(USD) RC TIMER	AS-CN002F	Connector XT60 Female	4,00	11	4	0	7	$ 0,52
18	ASCEND	(USD) RC TIMER	AS-CN002M	Connector XT60 Male	2,00	13	2	0	11	$ 0,52
19	ASCEND	(ZAR) RS COMPONENTS SA	AS-CN003M	Binder 620 3 Way Male w Cable (734-5338)..	2,00	6	0	0	6		R 190,66
20	ASCEND	(ZAR) RS COMPONENTS SA	AS-CN004F	Binder 620 3-way female panel mount connector (468-976)		6	0	0	6		R 108,45
21	ASCEND	(ZAR) RS COMPONENTS SA	AS-CN005	Binder 620 Snap-In IP67 adapter, pluggable ( 468-992)..	2,00	5	0	0	5		R 54,80
22	ASCEND	(USD) RC TIMER	AS-CN006	Amass AS150 Connector 7mm Gold Plated Spark Free Connector..	4,00	19	12	0	7	$ 5,48
23	ASCEND	(USD) CASTLE	AS-CN007	Connector CC Bullet 4mm	12,00	66	12	0	54	$ 1,98
24	ASCEND	(USD) RC TIMER	AS-CN008	Connector 4.0mm Bullet Male (charge leads)	8,00	72	8	0	64	$ 0,20
25	ASCEND	(ZAR) ALPINE GULF EQUIPMENT	AS-CS001	Ascend Case	1,00	1	0	0	1		R 21 782,00
26	ASCEND	(ZAR) EPSILON ENGINEERING SERVICES	AS-FR	ALTI Ascend Airframe	1,00	3	1	0	2		R 106 000,00
27	ASCEND	(ZAR) ESHEN & REID	AS-FR001	Serial Number Plate	1,00	4	1	0	3		R 25,00
28	ASCEND	(ZAR) PRO BOLT	AS-FR002	M4 Aluminum Dome Head Bolt..M4 x (0.7mm) x 25mm (LFB425)	4,00	0	1	0	-1		R 29,00
29	ASCEND	(USD) MACGREGOR INDUSTRIES LIMITED	AS-FU001	SE Fuel system V2	1,00	0	0	0	0	$ 86,62
30	ASCEND	(USD) BATTLEFIELD INTERNATIONAL INC.	AS-FU002	Battlefield Fuel Filler Plug, EL01Y, 02 x 04 RU, Brass/Alum (AA1A1S)	1,00	0	1	0	-1	$ 119,00
31	ASCEND	(USD) BATTLEFIELD INTERNATIONAL INC.	AS-FU003	Battlefield Refuel Valve EL01Y Coupler, 02 x 04 RU, Brass/Alum (AA1A9R)	1,00	0	0	0	0	$ 154,00
32	ASCEND	(USD) BATTLEFIELD INTERNATIONAL INC.	AS-FU004	Battlefield Inline Fuel Filter Repair Kit (BA7A3A-REP)	1,00	1	0	0	1	$ 70,00
33	ASCEND	(USD) BATTLEFIELD INTERNATIONAL INC.	AS-FU005	Battlefield Inline Fuel Filter 02x04 REHB Fuel Filter Assembly (BA7A3A)	1,00	-1	0	0	-1	$ 109,00
34	ASCEND	(USD) BATTLEFIELD INTERNATIONAL INC.	AS-FU006	Dual Panel Mount Hose Barb, 2 x 2.3mm Hose - Custom Made Airtube Connectors (BA6A7B)	2,00	6	0	0	6	$ 9,99
35	ASCEND	(ZAR) Aluma Tig Worx	AS-FU007	Ascend Motor Mnt Screw	4,00	24	0	0	24		R 0,00
36	ASCEND	(ZAR) Aluma Tig Worx	AS-FU008	Header Tank Lower V2	1,00	0	0	0	0		R 0,00
37	ASCEND	(ZAR) Aluma Tig Worx	AS-FU009	Header Tank Upper V2	1,00	0	0	0	0		R 0,00
38	ASCEND	(USD) MACGREGOR INDUSTRIES LIMITED	AS-PP001	Saito FG-14C (SAT14CFG)	1,00	1	1	0	0	$ 249,37
39	ASCEND	(USD) TIGER MOTOR	AS-PP002	T-Motor MN501 550KV Custom Motor	4,00	49	4	0	45	$ 84,90
40	ASCEND	(USD) TIGER MOTOR	AS-PP003	T-Motor Flame 60A ESC	4,00	4	4	9	9	$ 69,99
41	ASCEND	(ZAR) JK PRODUCTS	AS-PP004	Dubro3 Blade Spinner,2-1/2Inc,Black (DUB546)..	1,00	1	1	0	0		R 146,00
42	ASCEND	(ZAR) JK PRODUCTS	AS-PP005	Turbo Flow Pro Air Filter 21 (DYN2505)	1,00	0	0	0	0		R 100,00
43	ASCEND	(ZAR) JK PRODUCTS	AS-PP006	Hangar 9 12V Starter..	1,00	0	0	0	0		R 526,00
44	ASCEND	(USD) MACGREGOR INDUSTRIES LIMITED	AS-PP007	Spark Plug (SAIG20120)..	1,00	0	0	0	0	$ 22,76
45	ASCEND	(USD) MACGREGOR INDUSTRIES LIMITED	AS-PP008	Gasket Kit	1,00	3	0	0	3	$ 6,99
46	ASCEND	(USD) MASTER AIRSCREW	AS-PP009	Master Airscrew 12*8" Pusher Propeller..(MA.3B12X80R01)	2,00	1	1	0	0	$ 11,98
47	ASCEND	(USD) MASTER AIRSCREW	AS-PP010	Mater Airscrew CW (MA.3X13X12RB1)	3,00	14	0	0	14	$ 13,99
48	ASCEND	(USD) MASTER AIRSCREW	AS-PP011	Mater Airscrew CCW (MA.3X13X12NB1)	3,00	14	0	0	14	$ 14,99
49	ASCEND	(ZAR) NELCAM ENGINEERING	AS-PP012	Ascend Engine Mount	2,00	0	0	0	0		R 390,00
50	ASCEND	(USD) TIGER MOTOR	AS-PP013	Ascend Prop Adaper CW	2,00	4	0	0	4	$ 6,00
51	ASCEND	(USD) TIGER MOTOR	AS-PP014	Ascend Prop Adaper CCW	2,00	4	0	0	4	$ 6,00
Stock Order

Mini Sheet: Sheet X (2-40)

Alti Product and Pricing sheet - COBUS TEST.xlsx
	A	B	C	D	E	F	G	H	I
1	#	Type	StockCode	Description	AS_C_MOQ	UnitPriceZAR	CostInZAR	UnitPriceUSD	CostInUSD
2	15	ASCEND	AS-BX001	Ascend Freight Crate	1	R 1 550,00	R 1 550,00		$ 0,00
3	16	ASCEND	AS-CN007	Connector CC Bullet 4mm	12		R 0,00	$ 1,98	$ 23,76
4	17	ASCEND	AS-CS001	Ascend Case	1	R 21 782,00	R 21 782,00		$ 0,00
5	18	ASCEND	AS-FR	ALTI Ascend Airframe	1	R 106 000,00	R 106 000,00		$ 0,00
6	19	ASCEND	AS-FR001	Serial Number Plate	1	R 25,00	R 25,00		$ 0,00
7	20	ASCEND	AS-FR002	M4 Aluminum Dome Head Bolt..M4 x (0.7mm) x 25mm (LFB425)	4	R 29,00	R 116,00		$ 0,00
8	21	ASCEND	AS-FU002	Battlefield Fuel Filler Plug, EL01Y, 02 x 04 RU, Brass/Alum (AA1A1S)	1		R 0,00	$ 119,00	$ 119,00
9	22	ASCEND	AS-FU003	Battlefield Refuel Valve EL01Y Coupler, 02 x 04 RU, Brass/Alum (AA1A9R)	1		R 0,00	$ 154,00	$ 154,00
10	23	ASCEND	AS-FU005	Battlefield Inline Fuel Filter 02x04 REHB Fuel Filter Assembly (BA7A3A)	1		R 0,00	$ 109,00	$ 109,00
11	24	ASCEND	AS-FU006	Dual Panel Mount Hose Barb, 2 x 2.3mm Hose - Custom Made Airtube Connectors (BA6A7B)	2		R 0,00	$ 9,99	$ 19,98
12	25	ASCEND	AS-PP001	Saito FG-14C (SAT14CFG)	1		R 0,00	$ 249,37	$ 249,37
13	1	ASCEND	AS-PP002	T-Motor MN501 550KV Custom Motor	4		R 0,00	$ 84,90	$ 339,60
14	2	ASCEND	AS-PP003	T-Motor Flame 60A ESC	4		R 0,00	$ 69,99	$ 279,96
15	3	ASCEND	AS-PP004	Dubro3 Blade Spinner,2-1/2Inc,Black (DUB546)..	1	R 146,00	R 146,00		$ 0,00
16	4	ASCEND	AS-PP009	Master Airscrew 12*8" Pusher Propeller..(MA.3B12X80R01)	1		R 0,00	$ 11,98	$ 11,98
17	5	ASCEND	AS-PP012	Ascend Engine Mount	2	R 390,00	R 780,00		$ 0,00
18	6	ASCEND	AS-PP015	Ascend ESC Spacer	4		R 0,00	$ 3,00	$ 12,00
19	7	ASCEND	AS-PP016	T-Motor P14*4.8 (CW & CCW)	2		R 0,00	$ 30,90	$ 61,80
20	8	ASCEND	AS-RU001	Grommet EDPM 27x16x22x1	2	R 4,75	R 9,50		$ 0,00
21	9	ASCEND	AS-SV001	MKS HV6130 - Control surface servos	3	R 63,75	R 191,25		$ 0,00
22	10	ASCEND	AS-SV002	MKS HV6130H - Throttle Servo	1	R 63,75	R 63,75		$ 0,00
Ascend Base

Cell Formulas
Range	Formula
F2	F2	='Stock Order'!K14
G2:G22	G2	=E2*F2
H3	H3	='Stock Order'!J23
F4:F7	F4	='Stock Order'!K25
H8:H9	H8	='Stock Order'!J30
H10:H11	H10	='Stock Order'!J33
H12:H14	H12	='Stock Order'!J38
F15	F15	='Stock Order'!K41
H16	H16	='Stock Order'!J46
F17	F17	='Stock Order'!K49
H18:H19	H18	='Stock Order'!J52
F20	F20	='Stock Order'!K55
F21:F22	F21	='Stock Order'!J56
I2:I22	I2	=E2*H2

 

[Marc L]

My demonstration revamped for unique references in data sheets :​

Sub Demo1r()
        Dim R&, S&, Rf As Range
        Application.ScreenUpdating = False
    With Worksheets(1).[A1].CurrentRegion.Rows
        For R = 2 To .Count
        For S = 2 To Worksheets.Count
               Set Rf = Worksheets(S).[A1].CurrentRegion.Columns(3).Find(.Cells(R, 3).Text, , , 1)
            If Not Rf Is Nothing Then
                   Rf(1, 2).Resize(, 3).Value2 = Application.Index(.Item(R).Value2, 1, [{4,5,10}])
                   Rf(1, 6).Value2 = .Cells(R, 11).Value2
            End If
        Next S, R
    End With
        Application.ScreenUpdating = True
        Set Rf = Nothing
End Sub

 

[Marc L]

As it was not such a great idea to no keep the variables so this revised demonstration revamped is faster than the previous one :​

Sub Demo1r2d2()
        Dim R&, U$, V, W, S&, Rf As Range
        Application.ScreenUpdating = False
    With Worksheets(1).[A1].CurrentRegion.Rows
        For R = 2 To .Count
            U = .Cells(R, 3).Text
            V = Application.Index(.Item(R).Value2, 1, [{4,5,10}])
            W = .Cells(R, 11).Value2
        For S = 2 To Worksheets.Count
               Set Rf = Worksheets(S).[A1].CurrentRegion.Columns(3).Find(U, , , 1)
            If Not Rf Is Nothing Then
                   Rf(1, 2).Resize(, 3).Value2 = V
                   Rf(1, 6).Value2 = W
            End If
        Next S, R
    End With
        Application.ScreenUpdating = True
        Set Rf = Nothing
End Sub

 

[Marc L]

Last but not least, another way which may be a little faster :​

Sub Demo2()
        Dim R&, U$, V, W, S&, X
        Application.ScreenUpdating = False
    With Worksheets(1).[A1].CurrentRegion.Rows
        For R = 2 To .Count
            U = .Cells(R, 3).Text
            V = Application.Index(.Item(R).Value2, 1, [{4,5,10}])
            W = .Cells(R, 11).Value2
        For S = 2 To Worksheets.Count
            With Worksheets(S)
                    X = Application.Match(U, .[A1].CurrentRegion.Columns(3), 0)
                If IsNumeric(X) Then
                   .Rows(X).Columns("D:F").Value2 = V
                   .Cells(X, 8).Value2 = W
                End If
            End With
        Next S, R
    End With
        Application.ScreenUpdating = True
        Set Rf = Nothing
End Sub

 

[CobusVanWyk]

Hi Marc - maybe one day I can also write this code. You are right, it is faster (and faster!).
Thank you, dear friend, I appreciate all of your patience and time!!