Sorting data in Excel

C

Cartoper

I am a C/C++/.Net developer. I am working on a basic Excel
spreadsheet for my son's Boy Scout Troop. A friend gave me the
following code to copy the list of scouts names from one place, sort
it and paste it into another place but for some reason the sorting is
not working. Any suggestions?

Private Sub Workbook_Open()
Sheets("ScoutList08 & Sales-to-date").Select
Range("B2:B75").Select
Selection.Copy
Sheets("DataEntry").Select
Range("C5000").Select
ActiveSheet.Paste
Selection.Sort Key1:=Range("C5001"), Order1:=xlAscending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A6").Select
End Sub

Cartoper
 
J

Joel

I don't like using the select method. Look at the code below. I used cell
to select the entire worksheet for the sort range. I'm not sure which
worksheet you want to sort so I have both sets of code below

Private Sub Workbook_Open()
with Sheets("ScoutList08 & Sales-to-date")
.Range("B2:B75").Copy _
destination:=Sheets("DataEntry").Range("C5000").Select
.cells.Sort _
Key1:=Range("C5001"), _
Order1:=xlAscending, _
Header:=xlGuess
end with
End Sub


Private Sub Workbook_Open()
with Sheets("ScoutList08 & Sales-to-date")
.Range("B2:B75").Copy _
destination:=Sheets("DataEntry").Range("C5000").Select
Sheets("DataEntry").cells.Sort _
Key1:=Range("C5001"), _
Order1:=xlAscending, _
Header:=xlGuess
end with
End Sub
 
I

Incidental

Hi Cartoper

I'm not sure what is going on for you as i tested this code and it
works fine for me running Excel 2003 on XP. It sorts the list after
it has been pasted to the DataEntry Sheet but other than that it does
exactly what you described.

Steve
 
D

Dave Peterson

One more:

Option Explicit
Private Sub Workbook_Open()

Dim RngToCopy As Range
Dim DestCell As Range

Set RngToCopy = Worksheets("ScoutList08 & Sales-to-date").Range("B2:B75")

Set DestCell = Worksheets("DataEntry").Range("c5000")

RngToCopy.Copy _
Destination:=DestCell

With DestCell.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count)
.Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _
header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.CutCopyMode = False

End Sub

I changed the xlGuess to xlYes. Since it's your data, I bet you know if it has
headers or not. I wouldn't let excel guess.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top