Copy rows to different sheets

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

Hi,

I hope someone can help with this, as I've tried to solve it myself &
failed.

I have a spreadsheet with a (sorted) list in sheet1 (the number of
rows will vary from time to time but would have a maximum of 8,000). I
want to copy the entire row to another sheet in the workbook if the
data in row “b” a certain value.

For example row b could have “Value1”, “Value2”, “Value3” etc. (Max of
20 different values) and I would like to copy the row with the
matching value to sheets names “Value1”, Value2”.

I hope I have explained everything & thanks in advance for all
assistance.

Regards

John
 
Hi,

Right click the sheet tab with the data in, view code and paste the code
below in. It currently copies to sheet 3 so change to suit

Sub Sonic()
Dim R As Range, copyrange As Range
Dim V As Variant
Dim S As String
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
S = "aaa,bbb" 'Put your values here
V = Split(S, ",")
For Each R In Range("B1:B" & lastrow)
If Not IsError(Application.Match(CStr(R.Value), V, 0)) Then
If copyrange Is Nothing Then
Set copyrange = R.EntireRow
Else
Set copyrange = Union(copyrange, R.EntireRow)
End If
End If
Next R
If Not copyrange Is Nothing Then
copyrange.Copy Destination:=Sheets("Sheet3").Range("A1")
End If

End Sub

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top