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