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.

 

[azad092]

Hi mumps
good morning and have a nice day
I have share the both data entry and all data files

 

[azad092]

Login - Dropbox

Login to Dropbox. Bring your photos, docs, and videos anywhere and keep your files safe.

www.dropbox.com

Login - Dropbox

Login to Dropbox. Bring your photos, docs, and videos anywhere and keep your files safe.

www.dropbox.com

 

[Michael M]

Depending on your dataset size you could also use

Sub MM1()
    Dim LastRow As Long
    LastRow = Sheets("iState").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With Range("B3:B" & LastRow)
    .AutoFilter Field:=1, Criteria1:="Bills"
    .SpecialCells(xlVisible).Copy
    Sheets("iSummary").Cells(1, 1).PasteSpecial xlPasteValues
    .AutoFilter
End With
End Sub

 

[Michael M]

Depending on your dataset you could also use

Sub MM1()
    Dim LastRow As Long
    LastRow = Sheets("iState").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With Range("B3:B" & LastRow)
    .AutoFilter Field:=1, Criteria1:="Bills"
    .SpecialCells(xlVisible).Copy
    Sheets("iSummary").Cells(3, 2).PasteSpecial xlPasteValues
    .AutoFilter
End With
End Sub

 

[azad092]

Depending on your dataset you could also use

Sub MM1()
    Dim LastRow As Long
    LastRow = Sheets("iState").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With Range("B3:B" & LastRow)
    .AutoFilter Field:=1, Criteria1:="Bills"
    .SpecialCells(xlVisible).Copy
    Sheets("iSummary").Cells(3, 2).PasteSpecial xlPasteValues
    .AutoFilter
End With
End Sub

HI Miachael
thanks for your attention
I need a little change for destination file
i want to copy data into an other file instead of the sheet of same file
for example I am using a file named data entry and if condition true then the entire row should be copied into the specific sheet of other file named All data

 

[azad092]

Hi mumps
good morning and have a nice day
I have share the both data entry and all data files

Login - Dropbox

Login to Dropbox. Bring your photos, docs, and videos anywhere and keep your files safe.

www.dropbox.com

Login - Dropbox

Login to Dropbox. Bring your photos, docs, and videos anywhere and keep your files safe.

www.dropbox.com

 

[mumps]

Your links to Dropbox bring me to the Login page. I need the link to your files. After you upload the file to Dropbox, mark each file for sharing and you will be given a link to each file that you can post here.

 

[azad092]

Ok dear

Data Entry.xlsx

Shared with Dropbox

www.dropbox.com

All Data.xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

Both files you posted don't have any data so it makes it difficult to test a possible solution. Please attach files with data and explain in detail what you want to do referring to specific cells, rows, columns, sheets and workbooks using a few examples from your data (de-sensitized if necessary).

 

[azad092]

Both files you posted don't have any data so it makes it difficult to test a possible solution. Please attach files with data and explain in detail what you want to do referring to specific cells, rows, columns, sheets and workbooks using a few examples from your data (de-sensitized if necessary).

hi
good morning and have a nice day
there are two files one of them is data entry 1 in which we enter data (in attached file there are 160+entries but the entries might be about 400 too) in this file in Column G we select nature of emergency from a drop down list.
On the behalf of nature of emergency we want that all data of each emergency should be copied in the other file named All Data. and in All Data file there are 60+ sheets with the name of each nature of emergency.
for example in Data Entry File if we select the nature of emergency RTA (road traffic accident) the entire row should be copied into the RTA Sheet of All Data file and similarly all other emergencies
I hope you will understand

Data Entry 1.xlsm

Shared with Dropbox

www.dropbox.com

All Data.xlsm

Shared with Dropbox

www.dropbox.com