I'm not sure what line you're writing about:

If it's this one:
FoundCell.Offset(1).Copy Dest.Offset(, 3)
use:
dest.offset(,3).value = foundcell.offset(1).value

or if it's this line:

..Range("H" & Rows.Count).End(xlUp).Copy Dest.Offset(, 4)

I'd replace it slightly.

Add a declaration line (near the top)
dim RngToCopy as range

Then that single line
..Range("H" & Rows.Count).End(xlUp).Copy Dest.Offset(, 4)
becomes this block:

set rngtocopy = .Range("H" & Rows.Count).End(xlUp)
Dest.resize(rngtocopy.rows.count,rngtocopy.columns .count).Offset(, 4).value _
= rngtocopy.value





Kel wrote:

Thanks...Now, I have one more issue that I can't figure out. All the
cells in Column H are formulas. I didn't realize that it would make a
difference. How do I change this so that I get the value for the 2
cells that I need in column H? Here's the code that I'm using:

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

Also, the message box is still coming up, but pressing enter gets me
right through it. Not sure what that's about, but I'm willing to hit
enter if necessary.

Thanks again,
Kel

Dave Peterson wrote:
If that message is coming up either way, then this code was modified:

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

If you changed the code, you may want to post what you're using.



Kel wrote:

The message box is coming up either way, if balance is found or
not.
Then, the new sheet is being populated correctly, with the cell
below
the balance. All 5 columns are being populated.

Is there a way to just leave the cell blank and continue if balance
isn't found??

Thanks a million!!

Dave Peterson wrote:
That message box should only come up with Balance wasn't found:

But this line will execute whether or not it was found:

.Range("H" & Rows.Count).End(xlUp).Copy Dest.Offset(, 4)



Kel wrote:

Thanks. It works well, one small issue though...the message
box is
coming up even though it is populating the new sheet correctly.
Why is
the box coming up if it is finding the value?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson