code to copy/paste entire rows based on cell contents in column B

[Reddog94]

I'm trying to copy/paste rows (within a range of rows) based on the value in column B. If the value in column B is "Current Forecast", I would like to copy those entire rows and paste them below in the first available blank row.

Here is the code I have so far, but I keep getting various errors such as Variable not Defined on the first Target.

Can anyone see what I am missing? Thanks.

Sub CopyPaste_CurrFCST()
    Dim nxtRow As Integer
    
    If Target.Column = 2 Then
        If Target.Value = "Current Forecast" Then
            nxtRow = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row + 1
            Target.EntireRow.Copy _
            Destination:=Sheets(2).Range("A" & nxtRow)
        End If
    End If
End Sub

 

[tigeravatar]

Reddog94,

If you need a sub to get all rows and copy over:

Sub CopyPaste_CurrFCST()
    
    With Intersect(ActiveSheet.UsedRange, Columns("B"))
        .AutoFilter 1, "Current Forecast"
        .Offset(1).EntireRow.Copy Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilter
    End With
    
End Sub

How you had it setup was similar to a worksheet_change event. This is an example of what the code should look like for a worksheet_change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngChg As Range
    Dim chgCell As Range
    Dim rngCopy As Range
    
    Set rngChg = Intersect(Target, Columns("B"))
    
    If Not rngChg Is Nothing Then
        For Each chgCell In rngChg
            If chgCell.Value = "Current Forecast" Then
                If rngCopy Is Nothing Then
                    Set rngCopy = chgCell
                Else
                    Set rngCopy = Union(rngCopy, chgCell)
                End If
            End If
        Next chgCell
        If Not rngCopy Is Nothing Then rngCopy.EntireRow.Copy Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
    
End Sub

 

[Reddog94]

Ah, so I have fixed the object defined problem. Now I am getting a Type Mismatch error for the second if statement (If Target.Value = "current forecast" Then.

Updated code:

Sub CopyPaste_CurrFCST()
    Dim nxtRow As Integer
    Dim Target As Range
        Set Target = Range("Filter_Data")
    
    If Target.Column = 2 Then
        If Target.Value = "Current Forecast" Then
            nxtRow = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row + 1
            Target.EntireRow.Copy _
            Destination:=Sheets(2).Range("A" & nxtRow)
        End If
    End If
End Sub

 

[Reddog94]

The first code worked...halfway. I get run-time error 1004, selection not valid, now on the row that starts with .Offset(1).