VBA copy row of data based on column value

[fordmudslinger]

I have a spreadsheet that i would like to have a row copied from sheet 1 if the value in column f is "L" in that same row. then have the entire row pasted into sheet 2. Please help

 

[JustynaMK]

Hi fordmudslinger,

Try the following VBA code:

Sub CopyF()
    Dim i, lngLastRow         As Long

    lngLastRow = Sheets("Sheet1").UsedRange.Rows.Count
    
    For i = 1 To lngLastRow
        If Sheets("Sheet1").Range("F" & i).Value = "L" Then
            Sheets("Sheet2").Rows(i).Value = Sheets("Sheet1").Rows(i).Value
        End If
    Next i

End Sub

 

[fordmudslinger]

That works! Right now it inserts the date in the same row as sheet 1 on sheet 2. Is it possible to have the data inserted one after the other on sheet 2 without spaces between rows? Thanks!

 

[My Aswer Is This]

Try this:

Sub Filter_Me()
'No Table
'Modified 11-29-17 1:30 AM EST
On Error GoTo M
Application.ScreenUpdating = False
Dim ans As String
ans = "L"
    With ActiveSheet.Range(Cells(1, "F"), Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F"))
        .AutoFilter Field:=1, Criteria1:=ans
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Range("A1")
        .AutoFilter
    End With
    Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No rows with the value" & ans & " were found " & vbNewLine & "Or some other error occured"
ActiveSheet.AutoFilterMode = False
End Sub

 

[fordmudslinger]

That returns the msgbog with the dialog. There are "L"'s in column F.

 

[My Aswer Is This]

Do you have a sheet named "Sheet2"
Do you have some values in column "F"
Most times when users say scripts do not work is because users have done something wrong.
The script looks for "L" in column "F"

This is a very simple script and I have tested it.

 

[fordmudslinger]

Yes there are multiple "L's" in column F. I see at the top of the code says 'no table. The info is in a table; does that matter?

 

[My Aswer Is This]

Yes that could make things work different. You never said data was in a table.
What is the name of the table?

Yes there are multiple "L's" in column F. I see at the top of the code says 'no table. The info is in a table; does that matter?

 

[My Aswer Is This]

Assuming your table is named "Table1"
And assuming your table begins in column "A"
Try this:

Sub Copy_rows_in_a_table()
'Copy rows in a table
'Modified 11-29-17 12:05 PM EST
On Error GoTo M
Dim RngToCopy As Range
Range("Table1").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="L"
    Set RngToCopy = Selection.Offset(-1, 0).SpecialCells(xlCellTypeVisible)
    Selection.AutoFilter
    RngToCopy.Copy Sheets("Sheet2").Range("A1")
    Range("Table1").AutoFilter
    
Exit Sub
M:
    MsgBox "No Rows with your criteria found"
  End Sub

 

[fordmudslinger]

The table name is "Data" and it starts in the B column. The code you gave only copies the header of the table.