How to select and copy rows of data that contain a certain text w/t Macro?

[lsn11]

Hello,

I would like to create a macro that will scan sheet one for cells that contain the word "Complete", Copy the whole row, and paste it into another sheet (Sheet 2 - "Complete") without overwriting the rows in sheet 2. And Finally Deleting the copied "Completed" rows from sheet one.

I am having trouble finding a select row by certain text feature to start the macro with.

PLEASE HELP!

Text Word "Completed"
Sheet one name - "All PO'S"
Sheet two name - "Completed"

 

[JLGWhiz]

Hello,

I would like to create a macro that will scan sheet one for cells that contain the word "Complete", Copy the whole row, and paste it into another sheet (Sheet 2 - "Complete") without overwriting the rows in sheet 2. And Finally Deleting the copied "Completed" rows from sheet one.

I am having trouble finding a select row by certain text feature to start the macro with.

PLEASE HELP!

Text Word "Completed"
Sheet one name - "All PO'S"
Sheet two name - "Completed"

Would the word completed be found in just one column or randomly throughout the used range. If in one column, which?

 

[lsn11]

The word "Completed" is found in column H.

 

[JLGWhiz]

The word "Completed" is found in column H.

Sub cpyNdelStuff()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("All PO'S")
Set sh2 = Sheets("Completed")
sh1.UsedRange.AutoFilter 8, "Completed"
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
sh1.AutoFilterMode = False
End Sub

 

[lsn11]

Sub cpyNdelStuff()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("All PO'S")
Set sh2 = Sheets("Completed")
sh1.UsedRange.AutoFilter 8, "Completed"
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
sh1.AutoFilterMode = False
End Sub

Thank you !

An error messages is coming up on the 5th line where the stars are.

Sub cpyNdelStuff()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("All PO'S")
*****Set sh2 = Sheets("Completed")****
sh1.UsedRange.AutoFilter 8, "Completed"
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
sh1.AutoFilterMode = False
End Sub

 

[JLGWhiz]

Typo corrected:

Sub cpyNdelStuff()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("All PO'S")
Set sh2 = Sheets("Complete")
sh1.UsedRange.AutoFilter 8, "Complete"
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
sh1.AutoFilterMode = False
End Sub

also, if you will select the body of code you enter into the thread and then click on the # symbol in the tool bar in the top of the reply pane, it will put code tags on what you have selected and hold the code formatting.

 

[lsn11]

Typo corrected:

Sub cpyNdelStuff()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("All PO'S")
Set sh2 = Sheets("Complete")
sh1.UsedRange.AutoFilter 8, "Complete"
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.Range("H2", sh1.Cells(Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
sh1.AutoFilterMode = False
End Sub

also, if you will select the body of code you enter into the thread and then click on the # symbol in the tool bar in the top of the reply pane, it will put code tags on what you have selected and hold the code formatting.

Hello, Thank you for all the help. It is still getting stuck on the same area. Is there anywhere I can upload the document?

 

[JLGWhiz]

Hello, Thank you for all the help. It is still getting stuck on the same area. Is there anywhere I can upload the document?

You don't need to upload the document. Just tell me what message you are getting when it gets 'stuck' and what line of code is highlighted when you click debug. If the sheet names do not match exactly you will get an Error 9, subscript out of range message. This means that either you don't have a sheet by that name, the name is misspelled of the case is different between upper and lower or you have spaces in eithe the sheet tab or the code that are not in the other one. In your original post you use complete in one place and completed in another place for the same data item. You need to be consistent with what is actually in your worksheet and change the code to match the worksheet.

 

[lsn11]

Hello, I noticed that you were right, the "completed" sheet was actually Sheet3 but I fixed it to Sheet2. Here is the link to the file https://www.dropbox.com/s/8kd1aub1fbhy3hr/Customer Purchase Order Master List pratice (2).xlsm?dl=0.

With the change implemented. It goes through, but it is now only copy pasting the row of categories above the cells in the list.

thank you for all the help!

 

[JLGWhiz]

The sheet number is irrelevant. If the sheet name is correct, Excel will find the right sheet. The sh1 and sh2 are just variables that represent the full blown sheet references and the numbers attached to the variables have no bearing on the sheet position in the tab listing or index. The variable for 'All PO'S' could be sh20 and Excel would still see it as Sheets("All PO'S") and know where to find it. There are two key issues preventing the code from working. One is the sheet names. The other is the criteria. Both involve the word 'Complete' or 'Completed'. After looking at the screen shots in your link, it appears that the file name and criteria are both "Completed", so the code I originally posted should work. I have no idea why it is not working for you.