Posts by Walthobum

    Re: Trouble assigning macros and passing variables using userforms

    Thank you. This is markedly different from the code I have been playing around with. The clear button is a hangover from the workbook I have been playing with and is, as you say, of no consequence here. Basically it took me a while to realise that I only needed a boolean to hold the data I needed.

    It mgiht take me a while to get my head round this. I like to try to understand what it is I'm being taught rather than just take it as read. Thank you though, I appreciate your time. I'll let you know how I go.


    Can anyone recommend a resource that will help me to make sense of userforms? I'm really struggling, (not for the first time).

    At the beginning of my project I ask the user for input. I have used InputBox which has been fine for my purposes up to now.

    CurrentMth = InputBox("Enter the current month", "Current month entry")

    This is great as it stores the response as a variable which I can get my head around relatively easily and use wherever I need to. Obviously the weakness comes with end users entering bad data causing the code to crash or produce incorrect results.

    What I'm hoping to do is 'force' the user to make an entry based on a choice of two possible entries. These choices will be "Current" and "Prior".

    I can set up the userform with Option buttons and, after some faffing around, I can make it appear on screen with an appropriate line of code.


    The user form basically only has a choice between "Current" and "Prior", along with "OK", "Clear" and "Cancel" buttons. I can't for the life of me work out how to get the choice of "Current" or "Prior" to pass to a variable in my main Sub Routine. Is there anywhere that can explain this to me clearly, (and, it would appear, in language a simpleton would understand)? I've been round the track with userforms quite a few times in the past.

    As an aside I'm also struggling to save my workbooks when I'm working with userforms. Is there something I'm doing wrong there?


    I found some code that has been extremely useful in updating all pivot tables in a workbook to the same pivot cache. The code is below. I now need to exclude a single pivot table from those updated. It will be the same pivot table every time - i.e. it will have a pivot table name and should be held on the same sheet every time I need to update all other pivot tables.

    I'm struggling to work out how to do this though. Any thoughts?


    Sub ChangePivotCache()
    'Change pivot cache for all Pivot Tables in workbook
    Dim pt As PivotTable
    Dim wks As Worksheet
      For Each wks In ActiveWorkbook.Worksheets
        For Each pt In wks.PivotTables
            pt.CacheIndex = Sheets("POD Finance Analysis").PivotTables(1).CacheIndex
        Next pt
      Next wks
    End Sub

    Thanks in advance,


    I'm trying to extract specific data from a dataset and paste it into a named workbook using variables. I've used this technique previously to good effect but I seem now to be unable to return anything but the first column of data. Is there something I'm doing wrong with the advanced filter? I've changed the criteria it's looking up to, the range it's pasting to, the cell it uses as the base for the range select before the advanced filter and to no avail. It's driving me crackers. Any help greatly appreciated. I can post a small sample workbook too if that is useful, but for now, here's the code:

    Re: Copy, Paste Filtered Data from Source Workbook to Specific Workbook using Variabl


    can anyone see anything immediately and obviously wrong with this line of code. Apologies for taking it out of context - i.e. not showing the whole code - but I have used this code many, many times in the past without error and it's now producing an error. Which is kind of baffling when I don't think I've done anything to materially affect this specific line of code.

    Any help greatly appreciated.

    With Sheets("Data")
                .Range("DP2").Value = rCl.Value
                .Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                CriteriaRange:=.Range("DP1:DP2"), CopyToRange:=TargetRange, Unique:=False
            End With


    I have a large dataset. I am trying to filter this dataset by a criteria and copy the relevant data only into a template sheet. I'm trying to do this using variables for the sheet names. I seem to be right on the cusp of getting the macro to carry out the task but can't get it to loop through my variables. I have used a variant of this code many times before, (thanks to RoyUK for that one), but can't get the final piece of this code to work.

    Any help greatly appreciated. Code below:

    Re: Updating Filtered Dataset from a Different Sheet

    Thanks Luke. I really appreciate your help. I've manged to fix the problem. It was, er... a user issue. Me being a bit stoopid really. In the dataset I'm trying to filter on I have monthly data, head Mth1, Mth2, Mth3... as that's how it comes to me from the original data source. I have added above these column headers the numbers 1, 2, 3... in order that I can use them for calculations and lookups. All I had to do was move these one row up to make sure they didn't form part of the CurrentRegion and it worked. I'm busily creating a hundred charts in the front end dashboard now to slow the sheet to a standstill :)

    Next job is to user proof it from the other end when I send it out. Much tricker proposition. Thank you so much though. Really helpful.

    Re: Updating Filtered Dataset from a Different Sheet

    OK, next issue is that no data is being returned. I've used these Advanced Filters previously in VBA and gone mad trying to find the reasons why the macro runs, but doesn't find any data. Usually something to do with headings. Is that right?

    Re: Updating Filtered Dataset from a Different Sheet

    One thing from the code. I can work out the filtering. Names a rnage to filter, then finds the criteria from another named range and finally populates the filtered data in a destination range.

    What I'm struggling with is linking it to a worksheet change event. I can't see how you've done that. Any help?

    Re: Updating Filtered Dataset from a Different Sheet

    Thanks Luke. That definitely gets me started. I'm always amazed at how few lines of code it takes when someone knows what they're doing. Much aprpeciated. I've no doubt that before long every single one of my projects/models will have something like this in and everyone in the office will be thoroughly cheesed off/mightily impressed with it.

    Re: Updating Filtered Dataset from a Different Sheet

    Hi Luke,

    thanks for this. To be fair that doesn't look a bad solution and I'm sure I'll use it or something similar in future projects, but I'm still more interested in the VBA solution. The chart element isn't as important to me in this example file to be honest, (I'm OK-ish at charting), it's more about how to apply the drop down box choices in one sheet to a dataset in another. Then I can simply refer to the subtotals in the data sheet.

    I'm hoping that a small amount of code with an event - i.e. choosing the drop down - will apply the filter to the dataset. If it's possible to choose mroe than one option on the drop down that would be great but if not, that's not an issue. Any clue as to where to start? I'm happier trying to learn the method and then applying it to my specific project, (in that way I should gain an understanding of the process and be able to apply it in a range of future projects), rather than ask for the whole solution.

    I hope that makes sense.

    Re: Updating Filtered Dataset from a Different Sheet

    Hi Luke, (and anyone else),

    apologies for delay in ersponding to you on this. I have updated with an attachment which shows the kind of thing I'm hoping to acheive without attaching a huge file. Basically if you can give me some pointers I'm hoping I can work out how to apply it to my main workbook for myself. Give a man fish etc..

    Any help greatly appreciated.

    Re: Filtering Data and Copying to New Workbook

    And in a blinding flash of light as soon as I read through the code on here I worked it out for myself. I think that's the first time that has happened to me.

    There's no need for the second "rCl2" variable is there? Overcomplicating it. Happy to post the code if anyone is interested.

    Some time ago on here I was shown a fantastic technique that I have since used in many different ways. Essentially it takes a dataset and cuts it up into workbooks based on whatever parameter you choose to use. I'm now keen to see if I can extend this a little and filter 2 or more datasets on different sheets and save to the same workbook. This is the code I've got so far:

    I'm getting a compile error on the Next rCl line: Invalid Next Control variable reference.

    Any help greatly aprpeciated. I've been looking at for a while and can't figure it out.