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,

 

[joel]

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 i
to
Sheet8 (overwriting anything already there) and sort it in ascendin
order by
column C.
Rows 1 and 2 of both sheets are constants (headings) and therefore ca
be
omitted fron the copy/paste ranges.
Many thanks,

with Sheets("Sheet2")
LastRow = .Range("A" & rows.count).end(xlup).row
Set CopyRange = .rows("3:" & LastRow)
end with

with Sheets("Sheet8")
'clear sheet 8
.rows("3:" & Rows.count).delete

CopyRange.Copy _
Destination:=.rows(3)

LastRow = .Range("A" & rows.count).end(xlup).row
.rows("3:" & LastRow).sort _
header:=xlno, _
key1:=.range("A3"), _
order:=xlascending
end wit

 

[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