refresh sheet2 with a sorted data from sheet1

[lothario]

Hi,

I have a workbook:
a. With 2 sheets called s1 and s2.
b. s1 contains 15 columns and 800 rows of unsorted data beginning at
c5.
c. Every day s1 gets a few more rows of data.

I would like s2 to:
1. Contain a sorted version of the data in s1.
2. The sorting criteria is by column 3 (ascending) then column 7
(decending) and then column 12 (ascending).

I would like s1 to:
1. Contain a button called "Sort now".
2. When this button is clicked, all the data in s2 starting at c5 is
cleared and replaced with the sorted version of the data in s1.
3. s1 should remain untouched as this contains the source of the
original data.

Can you please give me the VBA code for this "Sort now" button?

Thanks,
Luther

 

[J.E. McGimpsey]

One way:

Public Sub CommandButton1_Click()
Dim rDest As Range

Set rDest = Sheets("s2").Range("C5")
rDest.Resize(Rows.Count - 4, 15).ClearContents
With Sheets("s1").Range("C5:Q" & _
Range("C" & Rows.Count).End(xlUp).Row)
Set rDest = rDest.Resize(.Rows.Count, 15)
rDest.Value = .Value
End With
rDest.Sort key1:=rDest(1), Order1:=xlAscending, Header:=xlNo
End Sub

 

[lothario]

Thanks