paste VALUES ONLY of range to next empty row on another sheet

[cdc01]

Have 2 sheets, source and destination. Source has data linked from othe
sheets (so there are formulas) and is updated frequently. I need t
copy this data to the next empty record in the destination sheet (th
values only, NO formulas) one by one, that is whenever i update th
source sheet, i want to copy this to the destination sheet so i have
permanent record of it. I can get this to work for a single record (
row), but not for multiple rows, even when i set the range to multipl
rows (see comments in code)...any help appreciated!

'code modified from Ron de Bruin
(http://www.rondebruin.nl/copy1.htm#Row)
'function to find the last row
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
'end function to find last row

Sub copyRE_Values_PasteSpecial()
Dim REsourceRange As Range
Dim REdestRange As Range
Dim Lr As Long
Application.ScreenUpdating = False
Lr = LastRow(Sheets("Destination")) + 1
' even though the range is set from 1:6 below, all it will copy is th
1st range????
Set REsourceRange = Sheets("Source").Range("1:6") '<<want to copy
rows
Set REdestRange = Sheets("Destination").Rows(Lr)
' take the sourcerange value only (NOT the formula!) and use tha
for the destination range value
REdestRange.Value = REsourceRange.Value '<<think this may be th
problem
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Su

 

[Ron de Bruin]

Look at the example that use resize to make both ranges
the same size.

http://www.rondebruin.nl/copy1.htm#Row

 

[cdc01]

Ron, you are the man! The method using resize worked like a charm!
Thanks!

Chad!