PDA

View Full Version : Want to search for value in column across multiple sheets and copy rows with value.



ToninuMT
03-30-2016, 12:32 AM
Hello I have no experience in coding at all and was hoping that I could find some help here please. What I want to do is go through all the sheets in the workbook and look for each row with a value of "NO" or "PART" in column E and copy the rows where either of these values exists to another sheet in the workbook called "INCOMPLETE" to generate a status report. Maybe it can be run when a button is clicked or a key combination pressed and also clears the "INCOMPLETE" sheet beforehand? Hope that someone can help me with this I would be very grateful.
Kind regards,
Anthony

Bob Phillips
03-30-2016, 01:22 AM
Public Sub CopyData()
Dim inc As Worksheet
Dim ws As Worksheet
Dim nextrow As Long
Dim lastrow As Long
Dim lastcol As Long
Dim i As Long

Application.ScreenUpdating = False

Set inc = Worksheets("INCOMPLETE")
inc.UsedRange.Cells.ClearContents
nextrow = 1

For Each ws In ThisWorkbook.Worksheets

If Not ws Is inc Then

lastrow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
For i = 1 To lastrow

If UCase(ws.Cells(i, "E").Value) = "NO" Or UCase(ws.Cells(i, "E").Value) = "PART" Then

lastcol = ws.Cells(i, ws.Columns.Count).End(xlToLeft).Column
ws.Cells(i, "A").Resize(, lastcol).Copy inc.Cells(nextrow, "B")
inc.Cells(nextrow, "A").Value = ws.Name & " - row " & i

nextrow = nextrow + 1
End If
Next i
End If
Next ws

Application.ScreenUpdating = True
End Sub

ToninuMT
03-30-2016, 02:54 AM
Thank you! It did work but I realized that I need to clean up the output a bit. How can I change this to just copy values (no formatting)? Thanks again!