Hi friends,
I am new to VBA but I find it immediately interesting! What I’m trying to do involves two worksheets. When I run the macro, I would like it to search through the worksheet “Screening Log” in column j to find all the records denoted “Y”. Making note of that row, I’d then like the macro to move across to the column C where I have stored a unique identifier for that record. I want that unique identifier pasted into the second worksheet “OT and Follow Up”.
I tried adapting some code as below, but to no avail. Basically what I want to avoid having to do is filtering the for “Y” in the first worksheet and copying them across. I’d rather make it a macro and reduce the number of steps in an already long process.
Any help?
Sub Update2()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("<wbr>Screening Log")
Set Target = ActiveWorkbook.Worksheets("OT and Follow Up")
j = 7 ' Start copying to row 1 in target sheet
For Each c In Source.Range("J7:J120") ' Do 120 rows
If c = "Y" Then
Range("c").Select
Source.Range("C" & (ActiveCell.Row)).Copy Target.Range(j, 2)
j = j + 1
End If
Next c
End Sub
I am new to VBA but I find it immediately interesting! What I’m trying to do involves two worksheets. When I run the macro, I would like it to search through the worksheet “Screening Log” in column j to find all the records denoted “Y”. Making note of that row, I’d then like the macro to move across to the column C where I have stored a unique identifier for that record. I want that unique identifier pasted into the second worksheet “OT and Follow Up”.
I tried adapting some code as below, but to no avail. Basically what I want to avoid having to do is filtering the for “Y” in the first worksheet and copying them across. I’d rather make it a macro and reduce the number of steps in an already long process.
Any help?
Sub Update2()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("<wbr>Screening Log")
Set Target = ActiveWorkbook.Worksheets("OT and Follow Up")
j = 7 ' Start copying to row 1 in target sheet
For Each c In Source.Range("J7:J120") ' Do 120 rows
If c = "Y" Then
Range("c").Select
Source.Range("C" & (ActiveCell.Row)).Copy Target.Range(j, 2)
j = j + 1
End If
Next c
End Sub