auto sort macro

I am trying to use a macro to sort a table of data on worksheet 3, whenever I 
update a value on worksheet 1 using Excel 2003 (Copy of macro below). 
Although the macro picks up the change in data on sheet 1 and runs the sort 
subroutine, nothing happens. I have discovered that by moving the tables to 
sheet 1 and repointing the macro to the data that it does work and while this 
is sufficient, I would like to know why the macro does not work when the 
tables are on sheet 3.

Thank you for any advice anyone can provide

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Worksheets(1).Range("B4:U32"), Target) Is 
Nothing) Then
        DoSort
    End If
End Sub

Private Sub DoSort()
    Worksheets(3).Range("A5:I10").Sort Key1:=Worksheets(3).Range("I5"), 
Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("H5"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("A13:I18").Sort Key1:=Worksheets(3).Range("I13"), 
Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("H13"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("A21:I27").Sort Key1:=Worksheets(3).Range("I21"), 
Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("H21"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("A30:I36").Sort Key1:=Worksheets(3).Range("I30"), 
Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("H30"), Order2:=xlDescending, Header:=xlYes
End Sub



0
Utf
2/10/2010 12:04:03 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
809 Views

Similar Articles

[PageSpeed] 42

A worksheet_change event, by definition, fires when you do something in a 
target cell on the same sheet as the macro. So, IF?? your macro is in sheet 
A it will fire when you change sheet A. Also, your macro could be more 
efficient using
with sheetA
..do this
end with

      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"mpleachy" <mpleachy@discussions.microsoft.com> wrote in message 
news:0D16BE2D-25D8-4B48-B9C7-D3588472586A@microsoft.com...
>I am trying to use a macro to sort a table of data on worksheet 3, whenever 
>I
> update a value on worksheet 1 using Excel 2003 (Copy of macro below).
> Although the macro picks up the change in data on sheet 1 and runs the 
> sort
> subroutine, nothing happens. I have discovered that by moving the tables 
> to
> sheet 1 and repointing the macro to the data that it does work and while 
> this
> is sufficient, I would like to know why the macro does not work when the
> tables are on sheet 3.
>
> Thank you for any advice anyone can provide
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>    If Not (Application.Intersect(Worksheets(1).Range("B4:U32"), Target) Is
> Nothing) Then
>        DoSort
>    End If
> End Sub
>
> Private Sub DoSort()
>    Worksheets(3).Range("A5:I10").Sort Key1:=Worksheets(3).Range("I5"),
> Order1:=xlDescending, _
>        Key2:=Worksheets(3).Range("H5"), Order2:=xlDescending, 
> Header:=xlYes
>    Worksheets(3).Range("A13:I18").Sort Key1:=Worksheets(3).Range("I13"),
> Order1:=xlDescending, _
>        Key2:=Worksheets(3).Range("H13"), Order2:=xlDescending, 
> Header:=xlYes
>    Worksheets(3).Range("A21:I27").Sort Key1:=Worksheets(3).Range("I21"),
> Order1:=xlDescending, _
>        Key2:=Worksheets(3).Range("H21"), Order2:=xlDescending, 
> Header:=xlYes
>    Worksheets(3).Range("A30:I36").Sort Key1:=Worksheets(3).Range("I30"),
> Order1:=xlDescending, _
>        Key2:=Worksheets(3).Range("H30"), Order2:=xlDescending, 
> Header:=xlYes
> End Sub
>
>
> 

0
Don
2/10/2010 1:26:29 PM
Reply:

Similar Artilces:

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

excel macro on startup from DOS
After I start excel from a DOS .BAT script I want a macro to then be automatically executed. (To do this with MS Word you just put /Mmacroname after the path name of the executable, but this does not seem to work) Excel doesn't support macro execution from the command line. As an alternative, you can name the macro Auto_Open and this will execute when the workbook is opened. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "roblit" <anonymous@discussions.microsoft.com> wrote in message news:02AFEE4E-6686-4434-A41D-1...

Sort highest by name
I have 55 teams in A1 to A55. I have their scores in B1 to B55. Is there a way to list the scores (highest to lowest) but giving their team names instead of all the numbers in order of the highest to the lowest? Help Obe 1 Why don't you just sort this data by column B in descending order? Select the range starting from cell B1 then just click the sort descending button on the toolbar. If you don't want to sort the data then you could use a formula. Are there any tie scores? What type of values are the scores? Are they whole numbers? Decimals? -- Biff Microsoft Excel MVP &qu...

Visual Basic Macros, relative position
Dear Experts, I am making a report, and I have a set of data, in a very looong row: MKT_VAL NET_ASSETS TOT_ASSETS 46 51 51 6233 6228 6228 Over 100 fields. I need this data to be changed to run down a column. MKT_VAL 46 6233 NET_ASSETS 51 6228 TOT_ASSETS 51 6228 I tried to make a macro to do this. It would be run after I copied and pasted the three column cells into another area. Sub ShiftNullData() ' ' ShiftNullData Macro ' Macro recorded 11/14/2006 by Rodger Lepinsky ' ' Keyboard Shortcut: Ctrl+q ' Range("A18").S...

Restoring auto-archive properties
I have set up my archive folders to archive each sub-folder of my inbox separately. I have since found this too convoluted to search for items and i wish to restore the default settings so that, my inbox and its subfolders now appear as a subfolder of the Archive folders drop down. How can i do this without losing any of my previous data/e-mails? Simply change the settings and move over any item that you want to be somewhere else now. If this results in multiple empty pst-file, then you can disconnect those via; File-> Data File Management... -- Robert Sparnaaij [MV...

Macro Message Box
Hi, I am trying to build a macro for a histogram. When the macro is complete, I am asked "Histogram - Some data will be hidden by embedded chart(s)." I can click OK or cancel. How can I build my OK response into the macro so that I do not need to manually respond? Thank you for reviewing my question. Remove the MsgBox from the macro if it is unnecessary. -- HTH Bob "lahuwm" <lahuwm@discussions.microsoft.com> wrote in message news:ADD9273F-E393-4573-A3F5-BEEB344D21B0@microsoft.com... > Hi, I am trying to build a macro for a histogram. When...

(OT sort of) Software for Journal or Diary
I would like to know of software that does a good job of letting me make bulleted (sometimes detailed) notes of things I did during the day. These will be reminders for my personal annual productivity reports, which I must complete for the job. * if it is calendar-based or -formatted, that would be nice * if the software is freeware, that would be useful * if the software is exportable to other formats, or has widely importable format, that would be useful (what if I use it in Linux, for example?) * I am wondering if also there is an online solution (ultimately "...

Help with macro #4
I have a workbook with 2 worksheets. Each contains a column of part numbers with associated data. What I want to do is look at a part number in the second sheet and bring over some of the data for that part number from the first sheet. I know I can't use Vlookup across 2 sheets, and in any case I don't want to re-sort the data as required by vlookup. (its in date order, not part number order) I'm guessing this can only be done with a macro. Can anyone help? Thanks. Hi! >I know I can't use Vlookup across 2 sheets, and in any case I don't want to >re-sort the d...

How do you remove auto indentation in text on PowerPoint?
I'm entering text on a 'Content' slide and the second line is automatically indented. I need this to line up on the leftside with the first line of text. The alignment is set for the left. Thanks. If you cannot see the ruler View > Ruler Then with the text selected move the bottom grey pointer to the left. Holding down CTRL may help. John "SherriG" <SherriG@discussions.microsoft.com> wrote in message news:E641735D-48C0-41BA-8DDE-783DAA26F4C1@microsoft.com... > I'm entering text on a 'Content' slide and the second line is ...

Macro lovers
anyone like writing macros? or know where i could findout how to write one for this? im after one that will do the following, with a button; Sheet name = Totals unhide colum A Select Cells A2:J84 Select>Tools>Options>View>Showplaceholders Select A1>dropdown>Show hide colum A Select>Tools>Options>View>Hide all End or does anyone know if this will make my rows auto hide or show when data is on them? colum A has =IF(OR(B2="",B2=0),"Hide","Show) guess i can test it while i wait :) Thanks again everyone! Steve You like macros. Try recor...

auto display name in shared calendar
We have set up a shared calendar in the Excahnge public folder area on which each employee will begin to record when they are in/out - on vacation etc... This will enable staff to determine who is available and who is not when phone calls come in for them etc... It seems to work fine except that when you create a calendar entry, you always have to enter your name - so that others will know who the entry is for. Is there any way to set it up so that when you create a calendar entry, your name is automatically displayed as the creator of the item on the calendar entry?? Thanks, Brad ...

Macro --- Icon
Using Macro in excel for the first time and set up routine with an icon .. But only see a "Smilley Face" Icon for Macro and now have two macros with same Icon.... How do I add new Icon Image(s) to differentiate one Macro from the Other.... Lenny EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com Lenny Right click on any toolbar and select customise. While the dialog is showing right-click on your smiley face and select change button image. (If these don't take your fancy you can 'roll your own by selecting edit button image... or you ca...

Auto detect the range depending up on the input date.
Hi All, I have an excel as below. If I give the date as input, suppose 6/1/2009, excel should do a count if function with the range starting from the 1st cell below the input date till the next date in the dates column. I am confused, please help. 6/1/2009 N New 291420 Roger N New 291425 Fred N New 291427 Lisa N New 291532 Lima N New 291741 Lisa N New 291531 Roger N New 291494 Roger 6/2/2009 N New 291532 Lima N New 291741 Lisa N New 291531 Roger N New 291494 Roger 6/3/2009 Thanks, S Where do you give the date as input? In the same sheet? ...

Sort by Next Occurrence of Recurring Appointment for Birthdays
Some things just really bug me...this was one of them. I wanted to be able to sort Birthdays and Holidays by the next time it occurs and Outlook just doesn't have a field for this. I'm not the only one who's bugged by this, and since I couldn't find code anywhere else on the web (but found lots of complaints), I thought I'd post my solution here. What this does is create a date field called "NextOccDate", looks to see if the date has already passed this year and if so, adds 1 to the year. You can drop this field from the user-defined fields into a table-view (...

Macro Question #13
Hello everyone, I have the following Code in Excel: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$G$4" Then Exit Sub Application.Goto Cells.Find(Target, after:=Target) End Sub I'm sure you are familiar with this??? Someone gave it to me her awhile ago. It just lets me type something in a cell and then it goe and finds whatever I typed on the worksheet. Okay, here is what I would like my code to do. Instead of going to th cell that contains that data, I would like it to return the value o the cell to the right of that cell in the cell und...

copy and paste macros
I have a workbook that has several macros in it. I created anothe similar workbook that uses the old workbook and macros as the basi platform for the new workbook. I copied and pasted the old macros t the new workbook. When I ran the macros after creating the ne workbook, everything worked fine. I emailed the program to my hom computer, and when I try to run the macros there, I get a run-tim error. What it looks like to me is the new workbook is trying t access the old workbooks macros. That is why it works on the compute I created the workbook on and not on my home computer, where I do ...

Sort/Custom List Help
I am working on a grade/attendance program for a teacher friend. Fo each class there are 3 worksheets. "Class 1" allows the teacher t enter students names as well as assignment scores. "Attendance 1 automatically imports the student names from "Class 1" allowing th teacher to track attendance. The last worksheet is "Reports 1" whic allows the teacher to prepare individualized reports on each student. I want to allow the teacher to sort the student list from "Class 1" t be alphabetical and then update the "Attendance 1" list and mov a...

Can user enable macros after accidently chosing "open disabled"?
I know this can't be accessed by code. But I'm wondering if, after a user has opened a workbook by accidently clicking "Disable Macros" at the warning, is there any menu command or such within the Excel environment that can be used to enable the macros? This is opposed to the user closing the workbook and re-opening (with a bit more cre, hopefully!), because this file can sometimes take a while to open. Ed Ed, Once the workbook has been opened with macros disabled, the only way to enable macros is to close and re-open it. HTH, Bernie MS Excel MVP "Ed" <e...

Excel, button for macro #2
Hello, I have a protected (real password... not just protection wihout password..) template with a button who star a macro. The macro change some informations, use the filters and print the result. When I push the button, the system ask for the password. Do you have ideas ? Thank you for your help. DG NB : I don't want put the password in the macro, means unprotect and protect the worksheet in the macro... ...

Auto Archive does not complete
Hello, i have a user who is running Office XP SP3 (Exchange 2000 SP3), where the auto archive feature fails to run on some folders inside his inbox. I cant seem to find any info on why this is happening either. If u run the archive feature manually everything works correctly. Any help would be greatly appreciated. ...

Recover a lost macro
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Can it be so easy to lose a macro? I intended to create a new macro and clicked 'create' while the name of the first macro in the alphabetic list was displayed. This was not my intent but the code of the original macro with that name appears gone forever. Is it really so easy, if not fatal, to lose a macro by simply forgetting to remove its name from the display before clicking create? <br><br>Is any recovery possible? <br><br>Please throw a life preserver. Hi Ron: Sorry, it's...

Word 2007 Macro Recoding Problem
I just tried a simple record macro and it did not record what I expected. I attempted to record a paste special unformatted text and it just recorded: Selection.PasteAndFormat (wdPasteDefault) This is not very close to what I did. Is there a way to get the macro recording to work like previous versions? ...

How to code it in macro? 05-22-10
Does anyone have any suggestions on how to code it in macro? There is a list of files under column A, I would like to create a macro to open - updating all links - save - close each file one by one for each file from A2 to A20. There is one condition to process each file, for example; The following files will not be processed until the last updated for this file C:\documents\A.xls is today, then open - updating all links - save - close following files, C:\documents\A1.xls in cell A2 C:\documents\A2.xls in cell A3 C:\documents\A3.xls in cell A4 The following files will ...

stopping a macro
I have a database that contains a complete list of cases for an area. For last month, there are approximately 15,000 cases listed; the number fluctuates month after month. This worksheet is entitled "Cases". I had written a macro that took all "A" cases off of that database and listed them on a worksheet entitled "A Cases", as staff initially needed a report to show only the "A" cases. The need now is to list the "A" cases and any related "F" or "M" cases for each "A" client. I am listing all of ...

macros: % to $ vice versa, one more que
Thanks McGimpsey. Question: Because I am a novice to VBA, how do I set the macro to only work amongst those target 3 cells, not the entire column ? Meaning, I have tons of other cells filled with other data, but I only want this macro to affect the three selected cells. Jacob (Would you prefer me to send you the document, if I'm being unclear?) "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message news:jemcgimpsey-66E540.20222229082004@msnews.microsoft.com... > One way: > > Private Sub Worksheet_Change(ByVal Target As Excel.Range) > Dim vResult A...