Find Data

[Stan]

I have VB code that creates the data below by using subtotals but I need to
find data in column A (7760) and copy and paste the data in another tab
(Schedule Counts). In other words, search for '7760' in column A and cut and
paste the row where '7760' exists. In the example below, I need to copy and
paste A2:C4.

I appreciate any help.

Column A Column B Column C
1) 7750 6:00am 11
2) 7760 6:00am 9
3) 7760 6:15am 13
4) 7760 6:45am 4
5) 7765 7:00am 6

 

[Jim Thomlinson]

Please don't multi post between the discussion groups... Most people
answering question monitor all of the groups.

Sub TestCopy()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirst As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:=16, _
LookAt:=xlWhole, _
LookIn:=xlValues)
Set rngPaste = Sheets("Schedule Counts").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
If rngFound Is Nothing Then
MsgBox "sorry... nothing to copy"
Else
strFirst = rngFound.Address
Set rngFoundAll = rngFound
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
rngFoundAll.EntireRow.Copy Destination:=rngPaste
End If

End Sub

 

[Stan]

Jim,

The code works perfectly although I also need to also copy data from sheet 2
and paste in the same location (Schedule Counts) but in coloumn E. I copied
the code and pasted it below and changed the sheet to 2 and the column
location on the tab 'Schedule Counts' and column 'E' but I get an error
saying the data and copy location are not the same size. In debugging, it
highlights the last line of the code 'rngFoundAll.EntireRow.Copy
Destination:=rngPaste'. I even made a new tab and tried to copy the data
from sheet 2 to that tab but recieved the same error message. Any ideas on
what I'm doing incorrectly?

In simplier terms, I need to copy data 7760 in Sheet 1 and 7750 in Sheet 2
to the tab 'Schedule Counts'. The data in sheet 1needs to go in column A
while the data from sheet 2 needs to go in column E.