VBA code for transferring between two sheets based on conditional criteria

hyd1956

New Member
Joined
Jun 26, 2020
Messages
40
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

Could you help with the following VBA code I'm stuck on. I am trying to do the following:

When column K (sheet 2) equals "Y" I want to transfer the additional information held in other columns from that row to another sheet. The value in V14 (sheet 2) needs to match the value in column H (sheet 1) and the value in column J (sheet1) must be "Available"

The code I have so far is:

Private Sub duplicatefindertransfer()

Dim duplicatesheet As Worksheet
Dim trackersheet As Worksheet

Set trackersheet = ThisWorkbook.Worksheets("Tracker")
Set duplicatesheet = ThisWorkbook.Worksheets("Duplicate Finder")

lastRowLookup = duplicatesheet.Cells(Rows.Count, "A").End(xlUp).Row
lastRowUpdate = trackersheet.Cells(Rows.Count, "I").End(xlUp).Row

For i = 1 To lastRowUpdate
valueToSearch1 = trackersheet.Cells(i, 10)
Valuetosearch2 = trackersheet.Cells(i, 8)
For t = 1 To lastRowLookup
If duplicatesheet.Cells(t, 11) = "Y" And duplicatesheet.Range("V14").Value = valueToSearch1 And "Available" = Valuetosearch2 Then
trackersheet.Cells(i, 10) = duplicatesheet.Cells(t, 14)
trackersheet.Cells(i, 11) = duplicatesheet.Cells(t, 1)
trackersheet.Cells(i, 12) = duplicatesheet.Cells(t, 2)
trackersheet.Cells(i, 14) = duplicatesheet.Cells(t, 15)
trackersheet.Cells(i, 15) = duplicatesheet.Cells(t, 16)
trackersheet.Cells(i, 20) = duplicatesheet.Cells(t, 17)
trackersheet.Cells(i, 21) = duplicatesheet.Cells(t, 18)
trackersheet.Cells(i, 22) = duplicatesheet.Cells(t, 19)
trackersheet.Cells(i, 41) = duplicatesheet.Cells(t, 20)
Exit For
End If
Next t
Next i
MsgBox ("Complete")
End sub
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Watch MrExcel Video

Forum statistics

Threads
1,114,472
Messages
5,548,241
Members
410,824
Latest member
Bobmn4
Top