Macro that refreshes/sort information

**SeanKosmann** · 10-27-2010, 03:13 PM

Attached is a simple soccer standing spreadsheet. What I want to accomplish is add a button on the first tab that reads "Refresh" (or anything else that it needs to be changed to), but what it needs to do is resort the standings, based on points (column F), with the highest number on the top.

I have never done a macro, so any advice would be great!

Thank you in advance!

**Domski** · 10-27-2010, 03:15 PM

Have you tried using the macro recorder? It should give you the code that you require.

Just start it recording and perform the action that you want and then stop it. You should then be able to attach the macro to a button from the Forms toolbar or an autoshape.

If you need more help post back.

Dom

**SeanKosmann** · 10-27-2010, 03:17 PM

Thanks Dom!

I have never even heard of it... I'll look into it. One issue I ran into, even when I highlighted the area and did a special sort, picking column F, it wasn't doing it properly. I'll keep trying and see if I can't unbreak it!

Thanks!

**Domski** · 10-27-2010, 03:20 PM

Have a go. The code the recorder comes up with is often far from perfect but good at replicating tasks like sorting a table. If your sort isn't working in the first place it's probably another issue.

I'll have a look at your sample.

Dom

**SeanKosmann** · 10-27-2010, 03:21 PM

The workbook is attached on the initial post, let me know what you see!

**Domski** · 10-27-2010, 03:37 PM

You have the sheet ref in some formula ,e.g. =COUNTIF(Sheet2!E:E,Sheet1!A2), when it's referring to the same sheet the formula is on. This can create a kind of absolute reference I think which seems to cause problems.

I've changed it and added some clean code to do the sorting which should do the trick.

Dom

**SeanKosmann** · 10-27-2010, 03:40 PM

So just taking out the "Sheet1" fixed it... man that's crazy... I would have spent hours thinking about that... thanks!

This is exactly what I needed!

**Domski** · 10-27-2010, 04:01 PM

If you wanted you could have the table sort each time the sheet is activated.

Please Login or Register  to view this content.

The code needs to go on the worksheet's code page to work. Right click on the sheet tab and select View Code. Paste the above onto the code page that appears.

Dom