Now it becomes what should happen if that "balance" isn't found.

This compiled, but I'm not sure it's what you want to do:

Option Explicit
Sub Copy_Data()
Dim w As Worksheet
Dim CopytoSheet As Worksheet
Dim FoundCell As Range
Dim Dest As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
Set Dest = [A1]
For Each w In ActiveWorkbook.Worksheets
If w.Name = "Copyto" Then GoTo NextSht
With w
.[A5].Copy Dest
.[A10].Copy Dest.Offset(, 1)
.[C3].Copy Dest.Offset(, 2)
Set FoundCell = .Columns("H:H").Find(What:="Balance", _
LookAt:=xlWhole)

If FoundCell Is Nothing Then
MsgBox "Not found!"
'what should be done
Else
FoundCell.Offset(1).Copy Dest.Offset(, 3)
End If


.Range("H" & Rows.Count).End(xlUp).Copy Dest.Offset(, 4)
End With
Set Dest = Dest.Offset(1)
NextSht:
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub



Kel wrote:

Thanks. I fixed it, now I have an object variable or with variable not
set. This is the text that is coming back highlighted.

.Columns("H:H").Find(What:="Balance", LookAt:=xlWhole).Offset(1).Copy
Dest.Offset(, 3)

--

Dave Peterson