Macro to append onto and then sort a pre-existing list

[Helen Sun]

Does anyone know where I can get the source code to do maybe a macro (I
think) that asks the user for an input, which then appends the input to
a pre-existing list and then sorts the new updated list?

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Dave Peterson]

I would think that any suggestion you get would be pretty generic and you'll
have to modify it.

So here's one!

Option Explicit
Sub testme()

Dim NextOpenCellInExistingList As Range
Dim myNewList As Range

With Worksheets("sheet2")
Set NextOpenCellInExistingList _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Set myNewList = Nothing
On Error Resume Next
Set myNewList = Application.InputBox(Prompt:="Select a range to append", _
Type:=8)
On Error GoTo 0

If myNewList Is Nothing Then
Exit Sub 'user cancelled
End If

myNewList.EntireRow.Copy _
Destination:=NextOpenCellInExistingList

With Worksheets("sheet2")
With .Range("a1:L" & .Cells(.Rows.Count, "A").End(xlUp))
.Sort key1:=.Columns(1), order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End With

End Sub

Lots of assumptions.

Sheet2 contains the original list.

The list always has data in column A (I use it to find the next available row).

Only one row of headers (for the list).

But it might get you started.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm