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