Copying a row to new sheet based on column value

[Guest]

Hi I have a worksheet that where I need to copy some of the rows to a new
sheet based on the value in a column.

Basically the sheet has 15 columns, and if the value in column H is either
ADD, ALA ALP, AMM or BEY I want to copy the entire row to a new worksheet so
I am left with two worksheets. One containing all rows of data and one
containg just those rows where column H contains one of the values above.

Any help would be much appreciated. I'm totally lost with this.

Regards
John

 

[Mike Fogleman]

This will add a sheet named "Criteria" and copy the needed rows to it.

Sub Copy()
Dim sLastRow As Long, dLastRow As Long
Dim sRng As Range, dRng As Range, c As Range
Dim ws As Worksheet
Dim FoundSheet As Boolean

sLastRow = Cells(Rows.Count, "H").End(xlUp).Row
Set sRng = ActiveSheet.Range("H1:H" & sLastRow)

For Each c In sRng
Select Case c.Text
Case "ADD"
GoTo A
Case "ALA"
GoTo A
Case "ALP"
GoTo A
Case "AMM"
GoTo A
Case "BEY"
GoTo A
A:
For Each ws In Worksheets
If ws.Name = "Criteria" Then
FoundSheet = True
End If
Next
If FoundSheet = False Then
Worksheets.Add.Name = "Criteria"
End If
dLastRow = Worksheets("Criteria").Cells(Rows.Count, "H").End(xlUp).Row
Set dRng = Worksheets("Criteria").Range("H" & dLastRow)
If dLastRow = 1 And IsEmpty(dRng) Then
c.EntireRow.Copy Worksheets("Criteria").Range("A1")
Else
c.EntireRow.Copy Worksheets("Criteria").Range("A" & dLastRow + 1)
End If
End Select
Next
End Sub

Mike F

 

[Guest]

Thank you Mike. Very much appreciated. You've saved me hours of work.

John

 

[mg_sv_r]

Hi

Hit a bit of a snag with the macro provided by Mike and I'm hoping someone
can help.

Mikes macro worked great but I have a problem when I have new rows added to
the first worksheet and have to run the macro again.

What I could do with now is the Macro only copying the rows to the worksheet
'Criteria' if the rows do not already exist. Any ideas? The same value should
never appear twice in column A so it would be good enough just to check that
column.

Thanks in advance for any help.

John

John

 

[mg_sv_r]

Just thought I better correct last post.

I was mistaken, column A could appear on the sheet more than once so I would
need to check entire row not just Column A value

Thanks
John

 

[Mike Fogleman]

I am not sure what you are comparing against. However, if you add additional
data rows to the original data, then run the macro, it will re-find
everything it found in the original data plus the new data and append it to
the Criteria sheet. This of course will duplicate everything it found from
the previous macro run. If this is what you want to avoid, then are several
approaches you can take. The simplest solution would be to clear the
Criteria sheet before the macro is ran, and let it report on your new data.
Mike F