Macro Novice

[Mon_Star]

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

 

[MARK858]

Try changing

Source.Range("C" & (ActiveCell.Row)).Copy Target.[COLOR="#FF0000"]Range[/COLOR](j, 2)

to

Source.Range("C" & [COLOR="#FF0000"]c[/COLOR].Row).Copy Target.[COLOR="#FF0000"]Cells[/COLOR](j, 2)

and remove the line below

Range("c").Select

 

[SQUIDD]

Hi

Try this on a copy of your workbook.

Sub test()
    For i = 7 To 120
        If Range("'Screening Log'!J" & i) = "Y" Then _
            Range ("'OT and Follow Up'!A" & Range("'OT and Follow Up'!A" & Rows.Count).End(xlUp).Row) + 1 = Range("'Screening Log'!C" & i)
    Next
End Sub

Dave

 

[Mon_Star]

Thanks Mark858 and SQUIDD. I'll give both a try. I don't have access to the workbook until I'm back at work though. Who knew I'd be so keen to return to work on Monday? :D

 

[MARK858]

Who knew I'd be so keen to return to work on Monday? :D

At least that makes one of us

 

[SQUIDD]

At least that makes one of us

i second that

dave

 

[Mon_Star]

Hi again folks,

Your code worked Mark858 but now I have a new problem The macro is picking up the first entry in the source spreadsheet instead of picking up on those with Y in column J.

Sub UpdateEnrol()

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("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

            Source.Range("C" & (ActiveCell.Row)).Copy Target.Cells(j, 2)

            j = j + 1

        End If

    Next c

   

End sub
[\code]

 

[MARK858]

I told you to make 2 adaptions to answer your specific query.

You were told to change

Source.Range("C" & (ActiveCell.Row)).Copy Target.Range(j, 2)

to

Source.Range("C" & [COLOR="#FF0000"]c.[/COLOR]Row).Copy Target.Cells(j, 2)

which you haven't done, see what you have in red below

Source.Range("C" & ([COLOR="#FF0000"]ActiveCell[/COLOR].Row)).Copy Target.Cells(j, 2)

 

[Mon_Star]

That was such a rookie mistake of mine, sorry Mark858! Much appreciated!!

 

[MARK858]

No problem, just a tip though copy and paste any code offered rather than adapt or re-type it and welcome to the forum.