sorting problem

[imoose]

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

 

[Barb Reinhardt]

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

 

[imoose]

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