Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofilter dead (Excel 2000)

    Macro copies a sheet from a file to allow for analysis. Original sheet is hidden and protected. The macro that copies the sheet to a new book also checks that DisplayAlerts is True, Calculation is Automatic, Events are enabled - that's all I can think of. All that happens when the user chooses AutoFilter is that the window shifts! The only way we can get AutoFilter to work is to save and close the file and close and re-open Excel. Something (?) is getting re-set so that the AutoFilter now works. There is no problem when I copy the sheet myself - but I don't want users to have this level of control over the file.
    Any suggestions will be gratefully received.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofilter dead (Excel 2000)

    Could you post the relevant code, or perhaps a stripped down copy of the workbook with the code and some dummy data?

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter dead (Excel 2000)

    Hans,
    I was unable to reduce the file size below 100kb without making it unworkable. There does not appear to anything remarkable about the data - 18 columns. The live data is up to 9500 records, but the problem persists even when I pare this down to 10 rows. I've attached the code.
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofilter dead (Excel 2000)

    Have you tried zipping the copy of the workbook?

    I don't understand the code you posted. It copies a worksheet to the clipboard, but then does nothing with it. The role of the name that is created in the code evades me.

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter dead (Excel 2000)

    Hans,
    The line of code, "Sheets("Data").Copy" automatically creates a new workbook to which I then assign the name "DischargeCopy".
    I would rather add code that saved and closed the copy of the file for the user than put him through zipping and unzipping.
    Ross

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofilter dead (Excel 2000)

    1. Sorry, I didn't pay close attention and didn't notice that I was in fact looking at a copy of the sheet. But AutoFilter worked OK on the new sheet for me.

    2. I didn't mean that the end user should zip anything, but that a zip file with the stripped down copy you made might be small enough to attach. It would be useful to see your copy, since I can't reproduce the problem using your code.

  7. #7
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter dead (Excel 2000)

    Hans,
    I'm sorry too - I should have realized you were suggesting I send you a zipped version of my file.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofilter dead (Excel 2000)

    It might be because I'm using Excel 2002 instead of 2000, but I can apply and use AutoFilter immediately after the new workbook has been created.

  9. #9
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter dead (Excel 2000)

    Hans,
    I'm not suprised. It works in Excel 2003 as well.
    I wonder if there's anyone else out there with your patience and with Excel 2000!

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Autofilter dead (Excel 2000)

    I also have 2003 and have no problem. What does the window shift TO when the AutoFilter is enabled? Have you tried immediately prompting the user by code to save the Workbook after it is created? Have you tried setting an Autofilter by Code on the copy Workbook?

    To improve your code in Sub ExtractDataSheet() you should probably set these data types:

    Dim Msg as String, Title as String, Style as Long, Response as Long

    and at the end sub, empty the worksheet objects:

    Set DischargeMaster = Nothing
    Set DischargeCopy = Nothing
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter dead (Excel 2000)

    John,
    Thanks for the code-clean-up suggestions.
    To start with you can see columns A to P. When you choose the AutoFilter, the window shifts to show columns G to R. The active cell does not change.
    Setting Autofilter in the macro likewise does not work.
    If I can't get this fixed, I will have the macro save & close the file and provide the user with a message as to where it has been saved and that it will replace the previous version. But there has to be a fix for this!!
    Thanks for your help.

  12. #12
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter dead (Excel 2000)

    John, Hans,
    I fixed it! All I did was remove the line of code that selects cell A2 - now explain that!

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Autofilter dead (Excel 2000)

    In my Excel 2003 version at 800 x 600, actually the display jumps to show columns K to R. I think the reason is just because selecting the AutoFilter causes the display to show that last column in the Auto Filter range. When I scroll back to column A or press Home, everything is fine.

    I don't know why selecting [A2] was messing it up, but your code could use some more work as far as clarifying when cell and range references are to DischargeMaster and Sheet "Data" - it's called 'fully qualifying' the object to it's parents. Also, when you post samples of your code, it's nice to disable the code that disables Save, so Loungers (such as me) don't have to hunt it down and comment it out. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>)
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Autofilter dead (Excel 2000)

    Did you mean when the user selects Autofilter on the worksheet "Data" attached to the DischargeDataTest Workbook, or the Copy? That could be an isue where you were looking at the worksheet "Data" attached to the DischargeDataTest Workbook when you thought you were looking at the Copy.

    I had no problem setting an AutoFilter on the Copy by inserting the red lines between your green lines in Sub ExtractDataSheet():

    <font color=448800> Sheets("Data").Copy
    Set DischargeCopy = ActiveWorkbook</font color=448800>

    <font color=red>With DischargeCopy.ActiveSheet
    .Range(Range("A1"), Range("A1").End(xlToRight)).AutoFilter
    .Range("A2").Select
    End With</font color=red>

    <font color=448800> DischargeCopy.Names.Add Name:="LookupRange", RefersToR1C1:= _
    "=OFFSET(Data!R1C1,0,0,COUNTA(Data!C1),18)"</font color=448800>
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter dead (Excel 2000)

    John,
    1. We are quite clear on which sheet is active when we want to filter, and the behaviour is consistent.
    2. The code you supplied does work, if I delete the line selecting cell A2 in the same way as I can choose to Autofilter when the sheet has been created with that line removed from the code.
    3. I have fully qualified all my variables with workbook and sheet names (as I should) but that's not it either.
    4. "DischargeMaster.Sheets("Data").Range("A2").Select " Put this in and you can't filter; leave it out and you can ... go figger!
    5. By the way, I assume your code for filtering is better if some columns don't have headings, otherwise, this will work:
    DischargeCopy.Sheets(1).Range("A1").AutoFilter

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •