Posts by Averilp

    Re: Copy Matching Rows To New Sheet


    Thanks Dave,


    I've started tidying up the code to reduce screen flicker based on your suggestion.


    With regards to the main question:


    Quote

    The user wants to be able to change the date range in the "overview" sheet and when there is data already in the other columns for these matching rows, have the input sheet update with old (copied??) info, if no previous data then add all 4 rows and sort... if previous date not in date range then delete entire row.


    Could anyone help with that part of it?


    Regards,
    Averil

    Re: Copy Matching Rows To New Sheet


    Hi Dave,


    Unfortunately I cannot attach the spreadsheet rather than include massive amounts of code in here as I am unable to strip it down further than it is and its still 87kb.


    Here is the code used to generate the input sheet this is generally pretty fast though there is still some .select 's in there...



    As per my original post, when a user wants to change the date range, I basically need to re-do the above for new date range, but bring over any data that may have already been entered in the rows "input" areas.


    Note: I need to have the actual data in this specific sheet as I use the sheet's code name throughout the rest of the code hence why I though I should make a copy of the data to a temp sheet, re-generate input sheet then find and bring over data from rows where Columns A, B and I match (user entered data is in columns C thru H).


    Thanks again - I hope I have explained sufficiently.


    Cheers,
    Averil

    I've searched but not found a solution that suits me thus far via VBA.


    I'm trying to eliminate any .select or .activate from my code to speed things up and reduce screen flicker.


    At the moment, I have a sheet created at the click of a button. Basically for every date exclusive of weekends, there are 4 entries, for each date there are the following entries AM Metro, PM Metro, AM Regional, PM Regional.


    The user wants to be able to change the date range in the "overview" sheet and when there is data already in the other columns for these matching rows, have the input sheet update with old (copied??) info, if no previous data then add all 4 rows and sort... if previous date not in date range then delete entire row.

    Re: Prevent Worksheet Change Event Causing Chain Reaction


    Hi Dave,


    Thanks for your response, and changing the thread title. I have looked at the new possible answers as well.


    I'm not too sure about how I would manage this as the line name cells on the three input sheets are all located at B1, and the line names in the overview sheet are C4:C6. If I put your code in the Workbook Object section, won't it also run the code when values change in the input sheets in C4:C6.


    So, if a value in B1 changes in sheets 27 to 29, the sheet name should change to that value, and C4, C5 or C6 on sheet8 should be changed. Alternatively if C4, 5 or 6 change on sheet8, B1 should change on sheet27, 28 or 29, and the sheet name also change accordingly.


    I must say that I don't really understand your code... I might need a little hand holding here. Where should I be putting the various bits of code? In an If block? Another select case?


    Cheers,
    Averil

    Hi all,


    I'd like the users to be able to change some detail in a couple of places and have it updated throughout the spreadsheet. Basically, the user can change the line name in any of the input sheets and the code changes the sheet name, and searches for the reference to the old name in the overview sheet and changes it accordingly.


    The problem I have at the moment is that I would also like the user to be able to change the line name from the overview sheet too... I am having trouble thinking how to have similar code in the "Overview" sheets Worksheet_Change event without getting into a big constant loop... e.g. if the line name is changed via code on the individual input sheets won't that then trigger the first code, which will trigger the second etc. etc.


    I have the following code in the ThisWorkbook section:



    And this is where I'm having trouble...



    Any help would be appreciated as I think I have fried my brain on this!


    Cheers,
    Averil

    Re: Advanced Filter Copy To Another Location


    Yes, all columns have headers, none are blank and no columns are hidden.


    I will keep bashing away at it today but will post sample if I am still having trouble.


    Thanks again,
    Averil[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi there,


    OK, I've tried... Please have a look at the spreadsheet attached if you have a moment.


    Ideally, once I have the filtering working for one, I expect to be able to have the code loop through and perform the same for each staff member and put it all together on the Invoice sheet broken down by employee. But I can work that out once I can find out why the Advanced Filtering isn't working in the first place.


    Thanks again.


    Averil

    Re: Advanced Filter Copy To Another Location


    Thanks so much for trying to help me!


    The CopyToRange is on the invoice sheet, the range being filtered is on the staff members sheet, and for reference the filter criteria is on another sheet altogether. I still experience the problem when I am copying to a "virgin" sheet/range.


    Basically what I have are individual sheets for each staff members' timesheet items, and multiple invoice sheets based on date ranges. Each staff members hours go in their sheet so all their data is in one place and we can then later tell how much time an individual spent on a given work package etc.


    So when I want to generate an invoice I'd like to bring over the data within the specified date range for each staff member into the invoice.


    Shortly, I will upload a sample of how I have been doing it (looping, copying, pasting etc.), and where I am at with the re-furb :)


    I had thought Advanced Filter would do the trick, and as previously mentioned it seems to work filtering in place just not copying to a different place.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Just wanted to advise that I will likely post example tomorrow...


    By running a few tests and implementing suggestions, I've narrowed the problem down to the dates being copied over but not the numbers etc associated with that row only in the particular range I am trying to copy to, even if I clear the contents of that range... it will work when copying to another part of that spreadsheet :angrypc:


    Aves

    Re: Advanced Filter Copy To Another Location


    Thanks - I have fixed the syntax but it is still having issues...


    I should have mentioned that when I run it via VBA the dates are being included but none of the corresponding values are included in the row. However if I try to run it through the Data --> Filter --> Advanced Filter I get the error I mentioned


    Cheers,
    Averil

    Re: Advanced Filter Copy To Another Location


    Thanks Mike,


    The code I'm using at the moment is:



    I'm copying and later deleting the header row (Row 7 on the sheet) as it's text is slightly different in the end product. However, I still get the problem when I leave it exactly the same.


    The criteria is a named range containing a start and end date.


    I have the following headings:


    DATES
    Meetings
    Project management and review
    Travel
    Task 1
    Task 2 'etc. (as many tasks as needed to include)


    I'm happy to upload a small sample if you think it will help, though I will have to run through and delete old code (previously mentioned looping) and unnecessary sheets first.


    Cheers,
    Averil

    Happy New Year!


    I seem to be having an odd problem with Advanced Filter. When I use the Copy to Another Location option I get an error that:


    "The extract range has an illegal or missing field name".


    As far as I can tell, there are no blank or missing fields. I did originally have a DATE field but I have renamed this in case it was a "reserved" name but still have the problem.


    When I used the filter in place it works correctly.


    I am trying to use Advance Filter as opposed to lots of looping, copying and pasting and this is the only snag. I suppose that I could filter in place then copy/paste the results to the other sheet but I'd like to see where I may be going wrong.


    Thanks in advance!


    Cheers,
    Averil

    Re: Check If Named Range Exists Before Delete


    Thanks Dave,


    I did have the On Error Resume Next in there but am debugging (like you told me :smile:) so wanted to stop getting the error I was getting and being curious I wanted to see what the workaround was.


    Cheers,
    AP

    Hi All,


    I'm trying to check if a named range "ActiveCells" exists before deleting it in VBA but can't seem to get the syntax right, I have tried:

    Code
    If Range("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete
    
    
    'AND
    
    
        If ActiveWorkbook.Names("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete


    Can anyone point me in the right direction? I have tried searching but I can only find threads about checking for named objects or about using the toolbars to add/delete named ranges.


    Thanks!
    Averil[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Sorry - I did find this one in the answers above after my post was submitted but not in my original search:


    http://www.ozgrid.com/forum/showthread.php?t=76641


    Thanks,
    Averil

    Re: Check If AdvancedFilter In Place


    Thanks Dave,


    I will take your advice on board as I keep working through this. I have been using ActiveSheet because other code selects a sheet (or creates new one) based on a users choice then this other code runs. Having said that though, I will think of ways I could work around it. I try not to use .activate and .select but I come across them every now and then... oops!


    I actually didn't know about re-running lines in debug mode - the instructor that I took a 2 day VBA course with (the sum total of my training I'm afraid :shocked:) didn't mention that.


    As ever, I'm in your debt.


    Averil

    Re: Show All Data Code Fails If No Advanced Filter Applied


    Thanks Jindon!


    You're a star. I kept thinking that it had to be similar to the check for AutoFilter:


    Code
    If ActiveSheet.AutoFilterMode = True Then
           MsgBox "They are visible"
        Else
           MsgBox "They are not visible"
        End If


    But I was wrong. Thanks for that - I'll be putting that in my ever increasing "VBA Code Gems" file :)


    Regards,
    Averil

    Hi again,


    I'm in the process of debugging some code at the moment and so have removed the On Error Resume Next's that I had in there.


    I've since gotten rid of this where it wasn't necessary, but I have a general question about using the ShowAllData method with Advanced filter.


    When I use:

    Code
    ActiveSheet.ShowAllData


    when there is no advanced filter applied, I get an error saying the method of Worksheet class failed.


    I have searched and cannot find a way to check in VBA if there are advanced filters applied, though I can find code that will check if AutoFilters are applied. This doesn't work with AdvancedFilters.


    Any thoughts?


    AP

    Re: Return Subtotal Result To Variable


    Thanks for looking Dave.


    When the code is run, old ranges are deleted and new ones are created. When I run it to the breakpoint I put in, then go back to the Excel sheet and GoTo "Cancelled" the correct filter is applied, and the correct range is selected.


    I will comment out the on error remove next's and try it again.


    AP[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi Dave,


    I seem to still be having the problem with the attached workbook. I have attached again for someone to please have a look. If you step through the first Sub in NEWCode (Filter_AM_Values) the result of the average calculation isn't being returned to the variable. E.g. if I enter:


    Code
    ? WorksheetFunction.Subtotal(101, Range("Cancelled"))

    in the immediate window, I get a result of 8.57142857142857E-02 but when moving to the next line of code when stepping through, the variable stays at zero.


    This is really baffling me!


    Thanks in advance,
    Averil

    Good afternoon gurus,


    I seem to be having a little trouble with some of my code. It has worked previously without error.


    The variables CMvN and SkMvN are DIMed globally. The only thing that I recall that I have changed is that I have changed them from Double to Long. Changed it back to test and still experiencing problem.


    Code
    ' Filter named range using a named range on another sheet
        Range("FilterRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("NormalMetroAM"), Unique:=False
        CMvN = WorksheetFunction.Subtotal(101, Range("Cancelled"))
        SkMvN = WorksheetFunction.Subtotal(101, Range("Skipped"))


    When I put a break point in above it then enter:


    Code
    ? WorksheetFunction.Subtotal(101, Range("Cancelled"))


    in the immediate window, the correct value (e.g. 8.57142857142857E-02) is returned. However, when I step through the code and go for example from CMvN line to SkMvN, the value isn't assigned to CMvN.


    Any thoughts?


    Regards,
    Averil