Copy/paste/sort

[JockW]

Sheet2 has data in cols A-F which are ordered in numeric order by colA. More
rows of data are added weekly.
I would like code which will copy all the data from Sheet2 and paste it to
Sheet8 (overwriting anything already there) and sort it in ascending order by
column C.
Rows 1 and 2 of both sheets are constants (headings) and therefore can be
omitted fron the copy/paste ranges.
Many thanks,

 

[Per Jessen]

Hi

Look at this:

Sub CopyPaste()
Dim TargetSh As Worksheet
Dim DestSh As Worksheet
Dim LastRow As Long

Set TargetSh = Worksheets("Sheet2")
Set DestSh = Worksheets("Sheet8")

LastRow = TargetSh.Range("A1:F" & Rows.Count) _
.SpecialCells(xlCellTypeLastCell).Row
TargetSh.Range("A3:F" & LastRow).Copy DestSh.Range("A3")

DestSh.Range("A2:F" & LastRow).Sort Key1:=DestSh.Range("C2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Regards,
Per