Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting data in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting data in Excel
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").Se lect .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").Se lect Sheets("DataEntry").cells.Sort _ Key1:=Range("C5001"), _ Order1:=xlAscending, _ Header:=xlGuess end with End Sub "Cartoper" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting data in Excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting data in Excel
One mo
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. Cartoper wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting data in Excel | Excel Discussion (Misc queries) | |||
Excel Data Sorting | Excel Worksheet Functions | |||
sorting excel data | Excel Worksheet Functions | |||
Sorting Excel Data | New Users to Excel | |||
Sorting data in Excel.. | Excel Worksheet Functions |