Jock,

Try this

Sub MoveData()

Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set SSh = ActiveWorkbook.ActiveSheet
Set DSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
LastRow = SSh.Cells(Cells.Rows.Count, "E").End(xlUp).Row
SSh.Range("A2:E" & LastRow).Copy
LastRow = DSh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
DSh.Range("A" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jock" wrote:

The following should work but it only copies cell A1 from the SSh (source) to
cell A1 on the DSh (destination).

Sub MoveData()

Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String

Set SSh = ActiveWorkbook.ActiveSheet
Set DSh = Workbooks("CMS Register of ClaimsAuto").Worksheets("Summary")

CopyRange = SSh.Range("A2:E" & Rows.count).End(xlUp).Copy

DSh.Range("A" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

Where did I go wrong?

--
Traa Dy Liooar

Jock