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

CobusVanWyk

New Member
Joined
Jun 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1TypeSupplierStockCodeDescriptionReorder Pt (Min)QtyOnHandQtyOnSalesOrderQtyOnPOAvailableUnitPriceUSDUnitPriceZAR
2ASCEND(ZAR) RF DESIGN CCAS-AN001Cable Assembly (Taisync) SMA-F RA BLK/H TO MMCX-M, RG316/U, 200mm(CA20FRABK25MDM2)1,002101R 115,56
3ASCEND(ZAR) RF DESIGN CCAS-AN002Cable Assembly (Taisync) SMA-F RA BLK/H to MMCX-M, RG316/U, 400mm(CA20FRABK25MDM4)1,002101R 120,19
4ASCEND(USD) SOUTH WEST ANTENNASAS-AN003Omni Antenna, Half Wave Dipole 2.1 - 2.5 GHz 2.15 dBI (1001-048)2,002200$ 115,00
5ASCEND(ZAR) RF DESIGN CCAS-AN004Ascend Taoglas 868 MHz Flex Circuit Antenna, 220mm, MCX-M (FXP280.07.0220M)..2,002002R 102,98
6ASCEND(ZAR) RF DESIGN CCAS-AN005Ascend Taoglas 915 MHz Flex Circuit Antenna, 220mm, MCX-M ..2,000000R 102,98
7ASCEND(USD) Taisync Technology LLCAS-AP0012.4G, Eth-in-Eth-out Module with USB2UART adaptor - Airside1,001100$ 550,00
8ASCEND(USD) SHENZHEN CAINAN TECHNOLOGY CO., LTDAS-AP002Alti CF Taisync Mount - Airside1,000000$ 0,00
9ASCEND(ZAR) The Aviation ShopAS-AP003Remove before Flight (PS35)-100-1R 0,00
10ASCEND(USD) SHENZHEN GREPOW BATTERY CO., LTDAS-BA26325CLiPo 2600mAh 3S 25C Gens Ace2,000000$ 19,49
11ASCEND(USD) SHENZHEN GREPOW BATTERY CO., LTDAS-BA33445CLipo 3300mAh 4S 45C..4,008008$ 24,92
12ASCEND(ZAR) INTERNAL ALTI R&DAS-BA352Lipo 3500mAh 2S RX GensAce..2,00121011R 249,18
13ASCEND(ZAR) JBS ENERGY SOLUTIONSAS-BA523Build 11.1V Li-ion 3S2P 5.6Ah Battery (Datalink)..2,000110R 895,00
14ASCEND(ZAR) REBULAS-BX001Ascend Freight Crate1,001001R 1 550,00
15ASCEND(USD) CASTLEAS-CN001FConnector Castle Creations 6.5mm Female1,00-110-2$ 2,65
16ASCEND(USD) CASTLEAS-CN001MConnector Castle Creations 6.5mm Male1,00-110-2$ 2,65
17ASCEND(USD) RC TIMERAS-CN002FConnector XT60 Female4,0011407$ 0,52
18ASCEND(USD) RC TIMERAS-CN002MConnector XT60 Male2,00132011$ 0,52
19ASCEND(ZAR) RS COMPONENTS SAAS-CN003MBinder 620 3 Way Male w Cable (734-5338)..2,006006R 190,66
20ASCEND(ZAR) RS COMPONENTS SAAS-CN004FBinder 620 3-way female panel mount connector (468-976)6006R 108,45
21ASCEND(ZAR) RS COMPONENTS SAAS-CN005Binder 620 Snap-In IP67 adapter, pluggable ( 468-992)..2,005005R 54,80
22ASCEND(USD) RC TIMERAS-CN006Amass AS150 Connector 7mm Gold Plated Spark Free Connector..4,00191207$ 5,48
23ASCEND(USD) CASTLEAS-CN007Connector CC Bullet 4mm12,006612054$ 1,98
24ASCEND(USD) RC TIMERAS-CN008Connector 4.0mm Bullet Male (charge leads)8,00728064$ 0,20
25ASCEND(ZAR) ALPINE GULF EQUIPMENTAS-CS001Ascend Case1,001001R 21 782,00
26ASCEND(ZAR) EPSILON ENGINEERING SERVICESAS-FRALTI Ascend Airframe1,003102R 106 000,00
27ASCEND(ZAR) ESHEN & REIDAS-FR001Serial Number Plate1,004103R 25,00
28ASCEND(ZAR) PRO BOLTAS-FR002M4 Aluminum Dome Head Bolt..M4 x (0.7mm) x 25mm (LFB425)4,00010-1R 29,00
29ASCEND(USD) MACGREGOR INDUSTRIES LIMITEDAS-FU001SE Fuel system V21,000000$ 86,62
30ASCEND(USD) BATTLEFIELD INTERNATIONAL INC.AS-FU002Battlefield Fuel Filler Plug, EL01Y, 02 x 04 RU, Brass/Alum (AA1A1S)1,00010-1$ 119,00
31ASCEND(USD) BATTLEFIELD INTERNATIONAL INC.AS-FU003Battlefield Refuel Valve EL01Y Coupler, 02 x 04 RU, Brass/Alum (AA1A9R)1,000000$ 154,00
32ASCEND(USD) BATTLEFIELD INTERNATIONAL INC.AS-FU004Battlefield Inline Fuel Filter Repair Kit (BA7A3A-REP)1,001001$ 70,00
33ASCEND(USD) BATTLEFIELD INTERNATIONAL INC.AS-FU005Battlefield Inline Fuel Filter 02x04 REHB Fuel Filter Assembly (BA7A3A)1,00-100-1$ 109,00
34ASCEND(USD) BATTLEFIELD INTERNATIONAL INC.AS-FU006Dual Panel Mount Hose Barb, 2 x 2.3mm Hose - Custom Made Airtube Connectors (BA6A7B)2,006006$ 9,99
35ASCEND(ZAR) Aluma Tig WorxAS-FU007Ascend Motor Mnt Screw4,00240024R 0,00
36ASCEND(ZAR) Aluma Tig WorxAS-FU008Header Tank Lower V21,000000R 0,00
37ASCEND(ZAR) Aluma Tig WorxAS-FU009Header Tank Upper V21,000000R 0,00
38ASCEND(USD) MACGREGOR INDUSTRIES LIMITEDAS-PP001Saito FG-14C (SAT14CFG)1,001100$ 249,37
39ASCEND(USD) TIGER MOTORAS-PP002T-Motor MN501 550KV Custom Motor4,00494045$ 84,90
40ASCEND(USD) TIGER MOTORAS-PP003T-Motor Flame 60A ESC4,004499$ 69,99
41ASCEND(ZAR) JK PRODUCTSAS-PP004Dubro3 Blade Spinner,2-1/2Inc,Black (DUB546)..1,001100R 146,00
42ASCEND(ZAR) JK PRODUCTSAS-PP005Turbo Flow Pro Air Filter 21 (DYN2505)1,000000R 100,00
43ASCEND(ZAR) JK PRODUCTSAS-PP006Hangar 9 12V Starter..1,000000R 526,00
44ASCEND(USD) MACGREGOR INDUSTRIES LIMITEDAS-PP007Spark Plug (SAIG20120)..1,000000$ 22,76
45ASCEND(USD) MACGREGOR INDUSTRIES LIMITEDAS-PP008Gasket Kit1,003003$ 6,99
46ASCEND(USD) MASTER AIRSCREWAS-PP009Master Airscrew 12*8" Pusher Propeller..(MA.3B12X80R01)2,001100$ 11,98
47ASCEND(USD) MASTER AIRSCREWAS-PP010Mater Airscrew CW (MA.3X13X12RB1)3,00140014$ 13,99
48ASCEND(USD) MASTER AIRSCREWAS-PP011Mater Airscrew CCW (MA.3X13X12NB1)3,00140014$ 14,99
49ASCEND(ZAR) NELCAM ENGINEERINGAS-PP012Ascend Engine Mount2,000000R 390,00
50ASCEND(USD) TIGER MOTORAS-PP013Ascend Prop Adaper CW2,004004$ 6,00
51ASCEND(USD) TIGER MOTORAS-PP014Ascend Prop Adaper CCW2,004004$ 6,00
Stock Order


Mini Sheet: Sheet X (2-40)

Alti Product and Pricing sheet - COBUS TEST.xlsx
ABCDEFGHI
1#TypeStockCodeDescriptionAS_C_MOQUnitPriceZARCostInZARUnitPriceUSDCostInUSD
215ASCENDAS-BX001Ascend Freight Crate1R 1 550,00R 1 550,00$ 0,00
316ASCENDAS-CN007Connector CC Bullet 4mm12R 0,00$ 1,98$ 23,76
417ASCENDAS-CS001Ascend Case1R 21 782,00R 21 782,00$ 0,00
518ASCENDAS-FRALTI Ascend Airframe1R 106 000,00R 106 000,00$ 0,00
619ASCENDAS-FR001Serial Number Plate1R 25,00R 25,00$ 0,00
720ASCENDAS-FR002M4 Aluminum Dome Head Bolt..M4 x (0.7mm) x 25mm (LFB425)4R 29,00R 116,00$ 0,00
821ASCENDAS-FU002Battlefield Fuel Filler Plug, EL01Y, 02 x 04 RU, Brass/Alum (AA1A1S)1R 0,00$ 119,00$ 119,00
922ASCENDAS-FU003Battlefield Refuel Valve EL01Y Coupler, 02 x 04 RU, Brass/Alum (AA1A9R)1R 0,00$ 154,00$ 154,00
1023ASCENDAS-FU005Battlefield Inline Fuel Filter 02x04 REHB Fuel Filter Assembly (BA7A3A)1R 0,00$ 109,00$ 109,00
1124ASCENDAS-FU006Dual Panel Mount Hose Barb, 2 x 2.3mm Hose - Custom Made Airtube Connectors (BA6A7B)2R 0,00$ 9,99$ 19,98
1225ASCENDAS-PP001Saito FG-14C (SAT14CFG)1R 0,00$ 249,37$ 249,37
131ASCENDAS-PP002T-Motor MN501 550KV Custom Motor4R 0,00$ 84,90$ 339,60
142ASCENDAS-PP003T-Motor Flame 60A ESC4R 0,00$ 69,99$ 279,96
153ASCENDAS-PP004Dubro3 Blade Spinner,2-1/2Inc,Black (DUB546)..1R 146,00R 146,00$ 0,00
164ASCENDAS-PP009Master Airscrew 12*8" Pusher Propeller..(MA.3B12X80R01)1R 0,00$ 11,98$ 11,98
175ASCENDAS-PP012Ascend Engine Mount2R 390,00R 780,00$ 0,00
186ASCENDAS-PP015Ascend ESC Spacer4R 0,00$ 3,00$ 12,00
197ASCENDAS-PP016T-Motor P14*4.8 (CW & CCW)2R 0,00$ 30,90$ 61,80
208ASCENDAS-RU001Grommet EDPM 27x16x22x12R 4,75R 9,50$ 0,00
219ASCENDAS-SV001MKS HV6130 - Control surface servos3R 63,75R 191,25$ 0,00
2210ASCENDAS-SV002MKS HV6130H - Throttle Servo1R 63,75R 63,75$ 0,00
Ascend Base
Cell Formulas
RangeFormula
F2F2='Stock Order'!K14
G2:G22G2=E2*F2
H3H3='Stock Order'!J23
F4:F7F4='Stock Order'!K25
H8:H9H8='Stock Order'!J30
H10:H11H10='Stock Order'!J33
H12:H14H12='Stock Order'!J38
F15F15='Stock Order'!K41
H16H16='Stock Order'!J46
F17F17='Stock Order'!K49
H18:H19H18='Stock Order'!J52
F20F20='Stock Order'!K55
F21:F22F21='Stock Order'!J56
I2:I22I2=E2*H2
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi, as you can link your workbook on a files host website like Dropbox for example …​
Hi Mark - I definitely can - will you kindly share your email address then I can either email it to you or send a Google Drive link? The file is about 340kb big, so nothing TOO serious.
 
Last edited by a moderator:
Upvote 0
You need to upload the file to a share site such as OneDrive, GoogleDrive, DropBox & mark for sharing. Then post the share link you are given to the thread.
 
Upvote 0
I have shared the file in question. My aim is to e able to ONLY update the first sheet (Stock Order), which will then automatically pull through all information to the relevant columns in the following sheets with the same codes...
 
Upvote 0
According to your attachment a VBA demonstration as a beginner starter :​
VBA Code:
Sub Demo1()
        Dim R&, U$, V, W, S&, Rf As Range, F&
        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).[A1].CurrentRegion.Columns(3)
                   Set Rf = .Find(U, , , 1)
                If Not Rf Is Nothing Then
                           F = Rf.Row
                    Do
                               Rf(1, 2).Resize(, 3).Value2 = V
                               Rf(1, 6).Value2 = W
                           Set Rf = .FindNext(Rf)
                    Loop Until Rf.Row = F
                End If
            End With
        Next S, R
    End With
        Application.ScreenUpdating = True
        Set Rf = Nothing
End Sub
 
Upvote 0
My demonstration can be simplified if each sheet #1 'StockCode' is unique within the data sheets #2-40 (can be only found once per data sheet) …​
 
Upvote 0
My demonstration can be simplified if each sheet #1 'StockCode' is unique within the data sheets #2-40 (can be only found once per data sheet) …​
Hi Mark

I ran the macro and it seems to be working perfectly. From the bottom of my heart, I want to thank you for this - I really appreciate your time and effort.
If I understand you correctly, there are duplicate "stock codes" found in data sheets #2-40? There should only be 1 code per data sheet #2-40, you are quite right in pointing that out. I have investigated and fix it, thank you.

Have an amazing day further!
 
Upvote 0

I did not write there is some duplicate but if each reference is unique in data sheets so my demonstration​
- which follows the already asked questions whatever the Excel forum or just reading obviously the VBA help of Range.Find method -​
can be optimized and simplified …​
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top