Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find, Match data and paste data between two workbooks | Excel Discussion (Misc queries) | |||
Find formatting doesn't work: "Excel cannot find data" | Excel Discussion (Misc queries) | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) | |||
FIND DATA WITHIN DATA (V-OR-H LOOKUP/FIND/MATCH?) | Excel Worksheet Functions |