Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting problem
I'm someone with no VBA experience who has inherited a workbook and has to
update it. The workbook has 3 worksheets - "Data Entry", "Intermediate", and "Output". The user enters data in various fields in Data Entry, and the data is pulled into Intermediate by formulas on the Intermediate worksheet. Then, the user clicks a button to execute a VBA macro to sort the data and paste the sorted data into the Output sheet. This all worked fine until I had to add a new row to the Data Entry sheet. I also added a set of corresponding formulas on the Intermediate page to pull the new row's data over. However, when I push the Sort + Output button, the new row is always pushed to the bottom and not sorted with the rest like it should be. Here's the sorting code: Sub sortcapturepaste() ' sortcapturepaste Macro ' Keyboard Shortcut: Ctrl+q ' Sheets("Intermediate").Select Range("A2:S83").Select ActiveWindow.ScrollColumn = 1 Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2:S83").Select ActiveWindow.ScrollColumn = 1 Selection.Copy Sheets("Output").Select Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A2").Select End Sub I've also noticed that, on the Intermediate sheet, the new row's data is displayed slightly differently - it is left-justified rather than right-justified. I thought it was a formatting issue, but I've used format painter on both the Data Entry sheet and on the Intermediate sheet and nothing changed (which I thought was weird). Any help is greatly appreciated. Thanks, imoose |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting problem
I suspect what's happening is that this line is what's causing your problems
Range("A2:S83").Select How about something like this Sub sortcapturepaste() ' sortcapturepaste Macro ' Keyboard Shortcut: Ctrl+q ' Dim aWS As Worksheet Dim myRange As Range Set aWS = Worksheets("Intermediate") Set myRange = aWS.Range("A2:S2") lrow = aWS.Cells(aWS.Rows.Count, "B").End(xlUp).Row Set myRange = myRange.Resize(lrow - myRange.Row + 1, myRange.Columns.Count) Debug.Print myRange.Address aWS.Select myRange.Select ActiveWindow.ScrollColumn = 1 myRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal myRange.Select ActiveWindow.ScrollColumn = 1 Selection.Copy Sheets("Output").Select Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A2").Select End Sub -- HTH, Barb Reinhardt "imoose" wrote: I'm someone with no VBA experience who has inherited a workbook and has to update it. The workbook has 3 worksheets - "Data Entry", "Intermediate", and "Output". The user enters data in various fields in Data Entry, and the data is pulled into Intermediate by formulas on the Intermediate worksheet. Then, the user clicks a button to execute a VBA macro to sort the data and paste the sorted data into the Output sheet. This all worked fine until I had to add a new row to the Data Entry sheet. I also added a set of corresponding formulas on the Intermediate page to pull the new row's data over. However, when I push the Sort + Output button, the new row is always pushed to the bottom and not sorted with the rest like it should be. Here's the sorting code: Sub sortcapturepaste() ' sortcapturepaste Macro ' Keyboard Shortcut: Ctrl+q ' Sheets("Intermediate").Select Range("A2:S83").Select ActiveWindow.ScrollColumn = 1 Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2:S83").Select ActiveWindow.ScrollColumn = 1 Selection.Copy Sheets("Output").Select Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A2").Select End Sub I've also noticed that, on the Intermediate sheet, the new row's data is displayed slightly differently - it is left-justified rather than right-justified. I thought it was a formatting issue, but I've used format painter on both the Data Entry sheet and on the Intermediate sheet and nothing changed (which I thought was weird). Any help is greatly appreciated. Thanks, imoose |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting problem
Barb,
Thank you for responding! I used your code, but still have the same problem - that new row is still all the way at the bottom. Maybe if I was a little more descriptive, that would help: When you type info into a row on the "Data Entry" page, that info is sucked into a row on the "Intermediate" page. When you've entered all the data you want, you push the "Sort + Output" button, and the rows on the Intermediate page are sorted and then copied over to the "Output" page. The info for the row that I added appears in row 78 of the Intermediate worksheet. When I push Sort + Output, this row doesn't seem to get sorted at all - it is still sitting down in row 78. So, if I entered data in 4 rows of the Data Entry sheet (one of them being my new row), and pushed the button, the other 3 rows would appear in the Output sheet as rows 1-3, then rows 4-77 would be blank, then my new row would still be sitting down there in row 78. It's bizarre. I thought it might be a "trying to sort numbers vs text" thing, but all the rows are formatted the same. I'm completely perplexed. Thanks for your time, imoose "Barb Reinhardt" wrote: I suspect what's happening is that this line is what's causing your problems Range("A2:S83").Select How about something like this Sub sortcapturepaste() ' sortcapturepaste Macro ' Keyboard Shortcut: Ctrl+q ' Dim aWS As Worksheet Dim myRange As Range Set aWS = Worksheets("Intermediate") Set myRange = aWS.Range("A2:S2") lrow = aWS.Cells(aWS.Rows.Count, "B").End(xlUp).Row Set myRange = myRange.Resize(lrow - myRange.Row + 1, myRange.Columns.Count) Debug.Print myRange.Address aWS.Select myRange.Select ActiveWindow.ScrollColumn = 1 myRange.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal myRange.Select ActiveWindow.ScrollColumn = 1 Selection.Copy Sheets("Output").Select Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A2").Select End Sub -- HTH, Barb Reinhardt "imoose" wrote: I'm someone with no VBA experience who has inherited a workbook and has to update it. The workbook has 3 worksheets - "Data Entry", "Intermediate", and "Output". The user enters data in various fields in Data Entry, and the data is pulled into Intermediate by formulas on the Intermediate worksheet. Then, the user clicks a button to execute a VBA macro to sort the data and paste the sorted data into the Output sheet. This all worked fine until I had to add a new row to the Data Entry sheet. I also added a set of corresponding formulas on the Intermediate page to pull the new row's data over. However, when I push the Sort + Output button, the new row is always pushed to the bottom and not sorted with the rest like it should be. Here's the sorting code: Sub sortcapturepaste() ' sortcapturepaste Macro ' Keyboard Shortcut: Ctrl+q ' Sheets("Intermediate").Select Range("A2:S83").Select ActiveWindow.ScrollColumn = 1 Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2:S83").Select ActiveWindow.ScrollColumn = 1 Selection.Copy Sheets("Output").Select Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A2").Select End Sub I've also noticed that, on the Intermediate sheet, the new row's data is displayed slightly differently - it is left-justified rather than right-justified. I thought it was a formatting issue, but I've used format painter on both the Data Entry sheet and on the Intermediate sheet and nothing changed (which I thought was weird). Any help is greatly appreciated. Thanks, imoose |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem Sorting | Excel Programming | |||
Sorting problem | Excel Discussion (Misc queries) | |||
Sorting Problem Need | Excel Programming | |||
Sorting problem | Excel Programming |