Help with VBA Code

joyrichter

New Member
Joined
Jun 17, 2023
Messages
31
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I only want to copy the rows from the 3 different sheets ,with the same reference on all 3 sheets
 
Upvote 0
sHEET 1
MPKODETAKCDXCXBSKACXHGRPCXSGRPCXVRDCXGEWIGBEDRAGCXKPCXVPCXRAK
140​
405​
A D-WD2035DOLMAR W&D V.CLEANERNN
1​
0​
3034.5​
3034.5​
7159.91​
WD04
140​
405​
A HONDA GXV160HONDA GXV160 5.5HP ENN06
1​
0​
5625.81​
5625.81​
9447.35​
WG05
140​
405​
A ICE BOX DRAINEVA COOL DRAIN BUNGOO
6​
0​
330​
55​
106.48​
WD12
140​
405​
A ICE BOX LATCHLATCH LOOP FOR ICE BOO
5​
0​
1463.1​
292.62​
528​
WD12
140​
405​
A ICE HANDLEEVA KOOL ICE BOX HAOO
5​
0​
275​
55​
106.48​
WD12
140​
405​
A ICE LARGEHINGE LARGE ICE BOXOO
5​
0​
275.05​
55.01​
91.67​
WD12
140​
405​
A KAWA FJ180V-87KAWASAKI VERTICAL SHUC
1​
0​
2824.23​
2824.23​
5868.5​
WG05
140​
405​
A KOHLER CV224KOHLER CV224 VERTICANN06
1​
0​
3227.4​
3227.4​
5422​
WG05

sHEET 2

405​
405100000​
1​
1​
VOORRAAD BESTELLINGR7731.08.2023AGPRO BROWSER 5LDD01BROWSER 5L
2​
1 420.00
2​
1 420.001 420.002 016.40
2​
900201711
405​
405100000​
2​
1​
VOORRAAD BESTELLINGR7731.08.2023MI CUTTEX 25LDD01CUTTEX 25L
1​
649.19
1​
649.19649.19908.87
1​
900201711
405​
405100000​
3​
1​
VOORRAAD BESTELLINGR7731.08.2023MI DEO BLOCKS 5LTDD01DEO BLOCKS 5LT
1​
320.85
1​
320.85320.85433.15
1​
900201711
405​
405100000​
4​
1​
VOORRAAD BESTELLINGR7731.08.2023ACCPWRTRMF32JJTRIM & TILT FLUID
1​
200.68
1​
200.68200.68291.30
1​
900201711
405​
405100000​
5​
1​
VOORRAAD BESTELLINGR7731.08.2023LUB15W50FS12JJYAMALUBE F SYNTHETIC
2​
257.05
2​
257.05257.05374.84
2​
900201711
405​
405100000​
6​
1​
VOORRAAD BESTELLINGR7731.08.2023LUB20W40FC04JJOIL, YAMALUBE FCW 20
9​
569.64
9​
569.64569.64810.00
9​
900201711

sheet 3
cdxCardex DescriptionMain GroupSub GroupLast GOB DateLast Sales DateCost Price12-24 Stock12-24 ValueOver-24 StockOver-24 Value
810 - 900 - HOEDSPRUIT
A D-WD2035DOLMAR W&D V.CLEANER WD-2035NN - HARDEWARE
2022-11-01​
0001-01-013 034.500.000.001.003 034.50
A HONDA GXV160HONDA GXV160 5.5HP ENGINENN - HARDEWARE
2022-11-01​
0001-01-015 625.810.000.001.005 625.81
A ICE BOX DRAINEVA COOL DRAIN BUNGOO - ONDERDELE
2022-11-01​
0001-01-0155.000.000.006.00330.00
A ICE BOX LATCHLATCH LOOP FOR ICE BOXOO - ONDERDELE
2022-11-01​
0001-01-01292.620.000.005.001 463.10
A ICE LARGEHINGE LARGE ICE BOXOO - ONDERDELE
2022-11-01​
0001-01-0155.010.000.005.00275.05
A KAWA FJ180V-87KAWASAKI VERTICAL SHAFT ENGINEUC - MOTORFIETSE
2022-11-01​
0001-01-012 824.230.000.001.002 824.23
A KOHLER CV224KOHLER CV224 VERTICAL SHAFTNN - HARDEWARE
2022-11-01​
0001-01-013 227.400.000.001.003 227.40
A TAN TRIM 750WTRIMMER 750W + 12M CABLEWW - TUINBENODIGDHEDE02
2023-04-12​
0001-01-01750.000.000.004.003 000.00
A TAN 1626260276TANK CAP -TANDEM XLINN - HARDEWARE
2022-11-01​
0001-01-0126.000.000.006.00156.00
A TAN 9100306160TANDEM PACER TORX XT160NN - HARDEWARE
2022-11-01​
0001-01-013 755.220.000.001.003 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
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

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