Copy rows from one sheet to another based on cell value

[craigg3]

I need to copy and paste rows from one sheet to another if the cell value in column B = Bills

Starting on sheet ("iState") at B3, if the cell = Bills, then I need it to copy that whole row to sheet("iSummary") starting on row 3

and then I need it to move to B4 and copy and paste f that cell value = Bill...

and continue until it gets to the end of iState sheet which will usually be about 250 records give or take. I played around with some code but wasn't getting anywhere with what I had. Thanks.

 

[mumps]

Try:

Sub CopyRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("iState").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    x = 3
    Dim rng As Range
    For Each rng In Sheets("iState").Range("B3:B" & LastRow)
        If rng = "Bills" Then
            rng.EntireRow.Copy Sheets("iSummary").Cells(x, 1)
            x = x + 1
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[craigg3]

Try:

Sub CopyRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("iState").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    x = 3
    Dim rng As Range
    For Each rng In Sheets("iState").Range("B3:B" & LastRow)
        If rng = "Bills" Then
            rng.EntireRow.Copy Sheets("iSummary").Cells(x, 1)
            x = x + 1
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Thanks, code works great. Is there a way to make it paste only the values and not the formulas within the copied cells?

 

[mumps]

Try:

Sub CopyRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("iState").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    x = 3
    Dim rng As Range
    For Each rng In Sheets("iState").Range("B3:B" & LastRow)
        If rng = "Bills" Then
            rng.EntireRow.Copy
            Sheets("iSummary").Cells(x, 1).PasteSpecial xlPasteValues
            x = x + 1
        End If
    Next rng
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[craigg3]

Try:

Sub CopyRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("iState").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    x = 3
    Dim rng As Range
    For Each rng In Sheets("iState").Range("B3:B" & LastRow)
        If rng = "Bills" Then
            rng.EntireRow.Copy
            Sheets("iSummary").Cells(x, 1).PasteSpecial xlPasteValues
            x = x + 1
        End If
    Next rng
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Works great, thanks again for the help!

 

[mumps]

You are very welcome.

 

[azad092]

Try:

Sub CopyRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("iState").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    x = 3
    Dim rng As Range
    For Each rng In Sheets("iState").Range("B3:B" & LastRow)
        If rng = "Bills" Then
            rng.EntireRow.Copy
            Sheets("iSummary").Cells(x, 1).PasteSpecial xlPasteValues
            x = x + 1
        End If
    Next rng
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Hi Mummps
I need this coding, but little different
I want if condition true then the entire row should be copied into a specific sheet of an other excel file
is it possible please guide me thanks

 

[mumps]

I want if condition true then the entire row should be copied into a specific sheet of an other excel file

What condition needs to be true and what are the names of the specific sheet and other Excel file? Please explain in detail referring to specific cells, rows, columns and sheets.

 

[azad092]

Data Entry file

 

[azad092]

all data file

All Data.xlsm
	D
6
Electric Shock