Hi,

I am totally new VBA. I need to copy data from Sheet1 and paste it to
Sheet2. The data in sheet1 changes every day and I need to append the
new data to page 2 on a daily basis.

I found the following code in this forum (by rondebruin) but is giving
an error when running it. Error= Subscript out of range.

I do not have the knowledge to edit it. Please help.


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

Sub copy_1_Values_PasteSpecial()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Application.ScreenUpdating = False
'Lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1,
0).Row
Lr = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1
'Lr = LastRow(Sheets("Sheet1")) + 1
With Sheets("Sheet6")
Set sourceRange = .Range("A1:A" & .Range("A" &
Rows.Count).End(xlUp).Row)
End With
'Set sourceRange = Sheets("Sheet6").Range("A1:C10")
Set destrange = Sheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


--
jijy
------------------------------------------------------------------------
jijy's Profile: http://www.excelforum.com/member.php...o&userid=15042
View this thread: http://www.excelforum.com/showthread...hreadid=526332