Macro – If Equal to Variable, Copy and move to Next

You could autofilter col B on Yes, then copy and paste the results to the other sheet.

If you want to automate that, record a macro, and then post back if you need help cleaning it up.

Hi milissadianna and welcome to the forum,

I agree with shg from above that it would be easy to do a filter but you asked for a Macro, so here it is. Also find it in the attached file.

Option Explicit

Sub MoveYesToSheet2()
Dim Sh1CurrentRow As Double
Dim Sh1LastRow As Double
Dim Sh2BlankRow As Double

Sh1LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

    For Sh1CurrentRow = 2 To Sh1LastRow
        If Worksheets("Sheet1").Cells(Sh1CurrentRow, "B").Value = "Yes" Then
            Sh2BlankRow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
            Worksheets("Sheet1").Rows(Sh1CurrentRow & ":" & Sh1CurrentRow).Copy _
            Destination:=Worksheets("Sheet2").Range("A" & Sh2BlankRow)
            
        End If
    Next Sh1CurrentRow
    
End Sub

See the attached.

Hi mellisadianne,
Find the attached with a Private code (behind the worksheet) that will build your list on the second page.

When you change any of the dropdowns of Yes/No it will cycle through and put the corrent info on your sheet two.

Hope this gives you a way of getting your job done easier.

I have no idea how it got behind sheet18 but here it is..

This code needs to be behind Sheet 2011 Rates.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoProjects As Double
Dim CurRow As Double
Dim BLankCol As Double

    If Target.Row > 4 Then
        If Target.Column = 5 Then
        Worksheets("Workscope_by_CTR").Range("c3:am3").ClearContents
        NoProjects = cells(5, "E").End(xlDown).Row
            For CurRow = 5 To NoProjects
                If cells(CurRow, "E").Value = "Yes" Then
                    BLankCol = Worksheets("Workscope_by_CTR").Range("AM3").End(xlToLeft).Column + 1
                    Range("B" & CurRow).Copy Destination:=Worksheets("Workscope_by_CTR").Range(cells(3, BLankCol).Address)
                End If
            Next CurRow
        End If
    End If

End Sub

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoProjects As Double
Dim CurRow As Double
Dim BLankCol As Double
Dim CRTBlankRow As Double 'New

    If Target.Row > 4 Then
        If Target.Column = 5 Then
        Worksheets("Workscope_by_CTR").Range("c3:am3").ClearContents
        
        Worksheets("CTR-01").Range("A38:f53").ClearContents ' New
        
        NoProjects = cells(5, "E").End(xlDown).Row
            For CurRow = 5 To NoProjects
                If cells(CurRow, "E").Value = "Yes" Then
                    BLankCol = Worksheets("Workscope_by_CTR").Range("AM3").End(xlToLeft).Column + 1
                    Range("B" & CurRow).Copy Destination:=Worksheets("Workscope_by_CTR").Range(cells(3, BLankCol).Address)
                
                    CRTBlankRow = Worksheets("CTR-01").Range("A36").End(xlDown).Row + 1 'New
                    Worksheets("CTR-01").Range("A" & CRTBlankRow) = Range("B" & CurRow) 'New
                    
                End If
            Next CurRow
        End If
    End If

End Sub

It replaces the code from before.

Hi Melissadianna,
If you record a macro and it does Range("A1").Select type of commands, it is working on the active worksheet. If you run that macro from a different sheet (the one it wasn't meant to run on) it can really mess things up.

In my code above I was very careful to specify which worksheet was the one being used.

My concern is that if you run a macro from a key combination like Ctrl-B you need to make sure you are on the correct sheet before letting the macro run.

This is my concern for making more macros using the recorder and simply calling them.

I hope that helps.