To Macro or not.

[philipt]

I have a page that I have written a macro for. It works great on my computer, but makes theirs stall. I think I can accomplish the same results without using a macro. I have a link to the excel document below but my macro is:

Private Sub Worksheet_Calculate()
Set Target = Sheet3.Range("A5:J29").SpecialCells(xlCellTypeFormulas)
Range("A5:J29").Sort _
Key1:=Range("J6"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Is there something wrong with this macro? Otherwise, Can someone recommend a way to have it sort by the MBAverage perhaps using the Small or Large code. I was using =VLOOKUP($J6,data,COLUMN(A1),FALSE) and made a data table on a sheet to hide, but I always got an #NA error.

http://www.philterry.net/new.xls


Thanks,
Phil

 

[patel45]

your goal is not clear for me

 

[philipt]

I wish to automatically sort a sheet based on either an amount in the "Market Basket Average" column of my work sheet or the "Rank" column of my worksheet. There are 8 columns that go along with the data that needs sorted. I added the Rank column to make writing my vbscript easier but if I don't need that column, it can be removed.