Help with VBA Code

[joyrichter]

Hi I am getting stuck on a code.

I need to compare 3 spreadsheets, and paste certain data that appear on all 3 sheets in a reconciliation.

can you please advise
Option Explicit

Sub Interface_total()
'coded by: J Richter
'Date: 31.01.2024
'Version: 1.0
'purpose: to consolidate the respectives obeslete stock items into one worksheet

'step 1: declare the worksheet variables
Dim ws140__405_Eindvoorraad_012024 As Worksheet
Dim ws140__405_GRN_202308_202309 As Worksheet
Dim ws900_Non_Moving_Stock As Worksheet
Dim wsRecon As Worksheet

'step 2: assign worksheets to the above variables
Set ws140__405_Eindvoorraad_012024 = Worksheets("140__405_Eindvoorraad_012024")
Set ws140__405_GRN_202308_202309 = Worksheets("140__405_GRN_202308_202309")
Set ws900_Non_Moving_Stock = Worksheets("900_Non_Moving_Stock")
Set wsRecon = Worksheets("Recon")

'Step3: Declare last row variables
Dim lastrow_140__405_Eindvoorraad_012024 As Long
Dim lastrow_140__405_GRN_202308_202309 As Long
Dim lastrow_900_Non_Moving_Stock As Long
Dim lastrow_Recon As Long

'step4: Determine last rows for Thabo and Cindy

lastrow_140__405_Eindvoorraad_012024 = ws140__405_Eindvoorraad_012024.Cells(Rows.Count, 1).End(xlUp).Row
lastrow_140__405_GRN_202308_202309 = ws140__405_GRN_202308_202309.Cells(Rows.Count, 1).End(xlUp).Row
lastrow_900_Non_Moving_Stock = ws900_Non_Moving_Stock.Cells(Rows.Count, 1).End(xlUp).Row

'Paste each row that contains "Mavs" in column A of Sheet1 sheet 2 sheet 3 into Sheet4
With Worksheets("140__405_Eindvoorraad_012024")
If .Cells(1, 1).Value = "Value.Cell(1,1)" Then
.Rows(1).Copy Destination:=Worksheets("Recon").Range("A" & 1)
1= 1 + 1
End If
End With
Next i

For i = 1 To LastRow
With Worksheets("140__405_GRN_202308_202309")
If .Cells(i, 1).Value = "Mavs" Then
.Rows(i).Copy Destination:=Worksheets("Recon").Range("A" & j)
j = j + 1
End If
End With
Next i


For i = 1 To LastRow
With Worksheets("900_Non_Moving_Stock")
If .Cells(i, 1).Value = "Mavs" Then
.Rows(i).Copy Destination:=Worksheets("Recon").Range("A" & j)
j = j + 1
End If
End With
Next i










'Step 9: Optional Housekeeping
Application.CutCopyMode = xlCopy

'Step10 : Remove copy border

wstotal.Activate
wstotal.Range("A1").Select 'Select A1 in total'





End Sub

 

[joyrichter]

I only want to copy the rows from the 3 different sheets ,with the same reference on all 3 sheets

 

[joyrichter]

sHEET 1

MPKODE	TAK	CDX	CXBSKA	CXHGRP	CXSGRP	CXVRD	CXGEWIG	BEDRAG	CXKP	CXVP	CXRAK
140​	405​	A D-WD2035	DOLMAR W&D V.CLEANER	NN		1​	0​	3034.5​	3034.5​	7159.91​	WD04
140​	405​	A HONDA GXV160	HONDA GXV160 5.5HP E	NN	06	1​	0​	5625.81​	5625.81​	9447.35​	WG05
140​	405​	A ICE BOX DRAIN	EVA COOL DRAIN BUNG	OO		6​	0​	330​	55​	106.48​	WD12
140​	405​	A ICE BOX LATCH	LATCH LOOP FOR ICE B	OO		5​	0​	1463.1​	292.62​	528​	WD12
140​	405​	A ICE HANDLE	EVA KOOL ICE BOX HA	OO		5​	0​	275​	55​	106.48​	WD12
140​	405​	A ICE LARGE	HINGE LARGE ICE BOX	OO		5​	0​	275.05​	55.01​	91.67​	WD12
140​	405​	A KAWA FJ180V-87	KAWASAKI VERTICAL SH	UC		1​	0​	2824.23​	2824.23​	5868.5​	WG05
140​	405​	A KOHLER CV224	KOHLER CV224 VERTICA	NN	06	1​	0​	3227.4​	3227.4​	5422​	WG05

sHEET 2

405​

405100000​

1​

1​

VOORRAAD BESTELLING

R77

31.08.2023

AGPRO BROWSER 5L

DD

01

BROWSER 5L

2​

1 420.00

2​

1 420.00

1 420.00

2 016.40

2​

900201711

405​

405100000​

2​

1​

VOORRAAD BESTELLING

R77

31.08.2023

MI CUTTEX 25L

DD

01

CUTTEX 25L

1​

649.19

1​

649.19

649.19

908.87

1​

900201711

405​

405100000​

3​

1​

VOORRAAD BESTELLING

R77

31.08.2023

MI DEO BLOCKS 5LT

DD

01

DEO BLOCKS 5LT

1​

320.85

1​

320.85

320.85

433.15

1​

900201711

405​

405100000​

4​

1​

VOORRAAD BESTELLING

R77

31.08.2023

ACCPWRTRMF32

JJ

TRIM & TILT FLUID

1​

200.68

1​

200.68

200.68

291.30

1​

900201711

405​

405100000​

5​

1​

VOORRAAD BESTELLING

R77

31.08.2023

LUB15W50FS12

JJ

YAMALUBE F SYNTHETIC

2​

257.05

2​

257.05

257.05

374.84

2​

900201711

405​

405100000​

6​

1​

VOORRAAD BESTELLING

R77

31.08.2023

LUB20W40FC04

JJ

OIL, YAMALUBE FCW 20

9​

569.64

9​

569.64

569.64

810.00

9​

900201711

sheet 3

cdx	Cardex Description	Main Group	Sub Group	Last GOB Date	Last Sales Date	Cost Price	12-24 Stock	12-24 Value	Over-24 Stock	Over-24 Value
810 - 900 - HOEDSPRUIT
A D-WD2035	DOLMAR W&D V.CLEANER WD-2035	NN - HARDEWARE		2022-11-01​	0001-01-01	3 034.50	0.00	0.00	1.00	3 034.50
A HONDA GXV160	HONDA GXV160 5.5HP ENGINE	NN - HARDEWARE		2022-11-01​	0001-01-01	5 625.81	0.00	0.00	1.00	5 625.81
A ICE BOX DRAIN	EVA COOL DRAIN BUNG	OO - ONDERDELE		2022-11-01​	0001-01-01	55.00	0.00	0.00	6.00	330.00
A ICE BOX LATCH	LATCH LOOP FOR ICE BOX	OO - ONDERDELE		2022-11-01​	0001-01-01	292.62	0.00	0.00	5.00	1 463.10
A ICE LARGE	HINGE LARGE ICE BOX	OO - ONDERDELE		2022-11-01​	0001-01-01	55.01	0.00	0.00	5.00	275.05
A KAWA FJ180V-87	KAWASAKI VERTICAL SHAFT ENGINE	UC - MOTORFIETSE		2022-11-01​	0001-01-01	2 824.23	0.00	0.00	1.00	2 824.23
A KOHLER CV224	KOHLER CV224 VERTICAL SHAFT	NN - HARDEWARE		2022-11-01​	0001-01-01	3 227.40	0.00	0.00	1.00	3 227.40
A TAN TRIM 750W	TRIMMER 750W + 12M CABLE	WW - TUINBENODIGDHEDE	02	2023-04-12​	0001-01-01	750.00	0.00	0.00	4.00	3 000.00
A TAN 1626260276	TANK CAP -TANDEM XLI	NN - HARDEWARE		2022-11-01​	0001-01-01	26.00	0.00	0.00	6.00	156.00
A TAN 9100306160	TANDEM PACER TORX XT160	NN - HARDEWARE		2022-11-01​	0001-01-01	3 755.22	0.00	0.00	1.00	3 755.22

I need i code that will run throuh all 3 sheets, check if a CDX code appears in all 3 sheets, and then past the cardex that appears on all 3 on a recon sheet

I hope this clarifies my problem