Afternoon, guys.



So, I have a purchase order spreadsheet, which gets a handful of new entries every day, and a few removed every day. Since it's constantly getting updated, I'd like to include a macro that would automatically sort all the content by "Supplier". Having essentially zero experience with macros, I don't even know where to start; could anybody help me out?

Cheers in advance.

Hi,
if this issue is still open, please attach an example with an explanation what you want to achieve. This is perhaps possible to solve without a macro.

Kind regards
Bjorn

I want this spreadsheet to automatically sort data by "Supplier" every time I open it.

Attached Images

untitled.JPG (142.6 KB, 22 views)

Can't you just make a macro with the sort by 'supplier' and just run the macro?

That's exactly what I want to do, but I don't know how. I've got virtually no experience with macros.

Edit: Actually, that's exactly what my code does now. It sorts the data alright when I manually run the macro, but doesn't do it when I set it to AutoOpen or AutoExit. Any ideas?

Why don't you select a cell in the Supplier column and click the Sort button? How would a macro improve on that?

Uh. Because it would automate it? One less step to have to do each time? Since it gets a whole heap of additions a day, it gets out of order quickly, so I want the macro to sort the spreadsheet by supplier upon opening/closing, so it's kept in order without me having to manually order it. Isn't this the point of macros?

Uh. I thought perhaps you were unaware that all you have to do is select one cell and click one button. Some people sort using a longer technique.

Hi,
I am more a "formula expert", but I looked around a little, stole some code and modified it

Here we go:
Right click the sheet tab. Select "View code".
Paste the code below. Modify sort column if wanted (A1 at the moment). Leave with Alt+Q.
Done!

Kind regards
Bjorn

Private Sub Worksheet_Change(ByVal Target As Range)
'Sort this sheet by column A
'Written by OzGrid Business Applications
'www.ozgrid.com
'small modification by Bjorn 2010-10-02

On Error Resume Next
Application.EnableEvents = False
Me.UsedRange.Sort _
Key1:=[A1], Order1:=xlAscending, Header:=xlYes, Orientation:=xlSortColumns
Application.EnableEvents = True
On Error GoTo 0

End Sub

No luck, man. Maybe it's because I didn't change the sort column (How do I?), but upon opening and closing, all the data remains the same.

Hi,
try replacing A1 with C1 in the macro, since you like to sort column C (which contains the supplier).

Kind regards
Bjorn

If you already have a macro that works, but just need for it to run when the file is opened, change the first line of the macro to

Sub Auto_Open()

Instead of Sub Whatever it is currently named

Alternatively, turn on macro recorder and make sure the Use Relative References button is off (it probably is). Name the macro Macro99. Select a cell in column C. Click either sort ascending or sort descending on the toolbar. Stop recording.

Tools > Macro > View macros
Select Macro99 and click Edit button
Change the first line, which is currently Sub Macro99()
to
Sub Auto_Open()

Okay guys, I think I've figured out the problem. The macro is working fine, but the reason it wasn't sorting the new additions (and still isn't) is because it only sets the range to whatever it was when I record the macro. As soon as I add in new orders, the range changes and the macro doesn't sort them all.

Pic attached is what I mean. If I set the macro while I've got Example 1 to 15, then add in 16 later, it'll only sort 1 to 15 upon opening. Any ideas, guys?

Attached Images

howdoifixtherange.JPG (169.1 KB, 14 views)

Consider using Dynamic Named Ranges to identify the area you want to sort.

Cheers,