#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 150
Default Find Data

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Find Data

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

--
HTH...

Jim Thomlinson


"Stan" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 150
Default Find Data

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.

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Stan" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find, Match data and paste data between two workbooks Chuckak Excel Discussion (Misc queries) 0 September 1st 06 06:59 PM
Find formatting doesn't work: "Excel cannot find data" Kasama Excel Discussion (Misc queries) 1 August 18th 06 01:40 PM
Despite data existing in Excel 2002 spreadsheet Find doesn't find AnnieB Excel Discussion (Misc queries) 1 June 16th 06 02:15 AM
The match and lookup functions can find literal data but not the same data referenced from a cell Jeff Melvaine Excel Discussion (Misc queries) 3 April 30th 05 01:29 PM
FIND DATA WITHIN DATA (V-OR-H LOOKUP/FIND/MATCH?) Jaladino Excel Worksheet Functions 0 February 23rd 05 12:22 AM


All times are GMT +1. The time now is 10:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"