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

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

John

"Mike Fogleman" wrote:

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
"mg_sv_r" wrote in message
...
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

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

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

"mg_sv_r" wrote in message
...
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

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


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

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

"mg_sv_r" wrote in message
...
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

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
"mg_sv_r" wrote in message
...
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


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

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

"mg_sv_r" wrote in message
...
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