joyrichter
New Member
- Joined
- Jun 17, 2023
- Messages
- 31
- Office Version
- 365
- Platform
- Windows
- MacOS
Good day
I need help with the following code;
I need to copy rows from one sheet to another sheet if certain information on the first sheet appears on 2 other sheets, the rows on the first sheets should automatically copy if the criteria is met.
For example: if Cell A1 in sheet 1(Non moving stock) = cdx123 and cdx123 appears on sheet 2 (Eindvoorraad) and sheet 3 (GRN), the information that is in the row of A on sheet 1 needs to copy over to a recon sheet.
This is the code I have managed to write so far:
'purpose: to consolidate the obeslete stock into one worksheet
Can you please help me
Thanking you in advance
I need help with the following code;
I need to copy rows from one sheet to another sheet if certain information on the first sheet appears on 2 other sheets, the rows on the first sheets should automatically copy if the criteria is met.
For example: if Cell A1 in sheet 1(Non moving stock) = cdx123 and cdx123 appears on sheet 2 (Eindvoorraad) and sheet 3 (GRN), the information that is in the row of A on sheet 1 needs to copy over to a recon sheet.
This is the code I have managed to write so far:
'purpose: to consolidate the obeslete stock into one worksheet
VBA Code:
'step 1: declare the worksheet variables
Dim wsRecon As Worksheet
Dim wsNon_moving_stock As Worksheet
Dim wsEindvoorraad As Worksheet
Dim wsGRN As Worksheet
'step 2: assign worksheets to the above variables
Set wsRecon = Worksheets("Recon")
Set wsNon_moving_stock = Worksheets("Non_Moving_Stock")
Set wsEindvoorraad = Worksheets("Eindvoorraad")
Set wsGRN = Worksheets("GRN")
'Step3: Declare last row variables
Dim lastrow_Recon As Long
Dim lastrow_Non_moving_Stock As Long
Dim lastrow_Eindvoorraad As Long
Dim lastrow_GRN As Long
Dim Cell As Range
Dim FinalRow As Long
'step4: Determine last rows for Non Moving Stock, Eindvoorraad, GRN
lastrow_Recon = wsRecon.Cells(Rows.Count, 1).End(xlUp).Row
lastrow_Non_moving_Stock = wsNon_moving_stock.Cells(Rows.Count, 1).End(xlUp).Row
lastrow_Eindvoorraad = wsEindvoorraad.Cells(Rows.Count, 1).End(xlUp).Row
lastrow_GRN = wsGRN.Cells(Rows.Count, 1).End(xlUp).Row
With Non_moving_stock
'Apply loop for column A until last cell with value
For Each Cell In .Range("A4:K" & .Cells(.Rows.Count, "A").End(xlUp).Row)
'Apply condition to match the "EINDVORRAAD" value
If Cell.Value = "EINDVOORRAAD" Then
'Command to Copy and move to a destination Sheet "RECON"
.Rows(Cell.Row).Copy Destination:=Recon.Rows(FinalRow2 + 1)
FinalRow2 = FinalRow2 + 1
'Apply condition to match the "GRN" value
ElseIf Cell.Value = "GRN" Then
'Command to Copy and move to a destination Sheet "Recon"
.Rows(Cell.Row).Copy Destination:=Recon.Rows(FinalRow2 + 1)
FinalRow2 = FinalRow2 + 1
End If
Next Cell
End With
Can you please help me
Thanking you in advance
Last edited by a moderator: