Search, Copy, Paste Macro in Excel
 

Having trouble with macro. I want macro to search all sheets in
workbook, find a word, copy the cell and adjacent cells with that word,
then paste the data into another workbook.

Here are the steps I follow:
*Open two documents, one called Hierarchy.xls and one called
Harvest.xls
*In Harvest.xls, I have the appropriate cell focused
*Switch to working in Hierarchy.xls
*Cmd+f(find)Find what:searchwordWithin:WorkbookSearch:By RowsLook
in:Formulas (match case & find entire.. are not checked)Find
NextClose
*Hierarchy.xls has 33 sheets. I make sure that the cell focus is on A1
of each sheet.
*Select the first worksheet
*ToolsMacroRecord New Macro
*Macroname:Macro2Shortcut Option+Cmd+qStore in: This WorkbookOK
*Relative reference is depressed
*Cmd+f(find)
*Click Find Next
*Click Close
*Left arrow onceHold Shift, right arrow twice (effectively selecting
the adjacent cells to the found cell)
*Cmd+c(copy)
*WindowHarvest.xls(switches to other workbook)
*Cmd+v(paste)
*down arrow once (gets to next row, ready for future applications of
macro)
*WindowHierarchy.xls(switches to other workbook)
*down arrow once (gets to next row, ready for future applications of
macro)
*Esc (gets rid of marquee on selection)
*Click Stop Macro (macro is complete)
*Go back to first sheet (search switched to second sheet)
*Cmd+option+q (activates macro)

First couple times I use the macro, it works fine, but after the third
time, I get error: "Run-time error '1004' Method 'Offset' of object
'Range' failed.

*Click Debug

"ActiveCell.Offset(0,-1).Range("A1:C1").Select" is highlighted.

Here is the complete text of the vba macro:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/3/2006 by L
'
' Keyboard Shortcut: Option+Cmd+q
'
Sheets("Sheet2").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, -1).Range("A1:C1").Select
Selection.Copy
Windows("Harvest.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("HIERARCHY.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub

Notes: The first line troubles me, because I think the macro is
automatically switching to the second sheet, when it should just be
searching and not switching sheets.