Posts by Walthobum

    Re: Updating Filtered Dataset from a Different Sheet


    Thanks Luke. I'm not a big fan of pivot charts personally so I wanted to avoid them here. They will scare my end users too. Intrigued by the intermediate sheet, mainly as I don't fully understand what you mean. I might already be running something similar so it could be a goer. Mostly interested in the potential for a VBA solution though. It would be cleaner and would run in the background meaning less chance for the end user to accidentally corrupt the sheet. Also I'm running a few routines in the sheet anyway so it feels right.


    Any tips or should I get something uploaded?

    I have a dataset with various description fields and various numeric/financial fields. This is housed in a relatively stragithforward worksheet called, for example, Data. I want to be able to view financial data on a chart on a another sheet. Is there any way through the use of dropdown boxes, (I want to be able to limit users to particular fields), to filter the Data sheet from another sheet called, for example, Chart.


    At the top of the Data sheet I have add a row with =subtotal() formulae for numeric/financial values. The idea is that I can simply point to these values on the Chart sheet which then update the charts with the correct data as it's been subtotalled in the Data sheet.


    I hope that makes sense. I can upload a small sheet with the type of thing I'm trying to achieve if that would help.


    Cheers.

    Re: Populate a range with the results of a userform listbox


    Agreed, I could have explained myself better. I went with the code below. Is there a material difference between the two? The definition of material I suppose revolves around run time, whether the code does what it's intended to and whether mine falls over. It doesn't appear to. Thank you for your help though, very much appreciated.



    Re: Populate a range with the results of a userform listbox


    Thanks cytop. One quick thing is that I need the items listed starting from range "AA2". So if there were 3 items selected, A, B and C then cell AA2 would show "A", cell AA3 would show "B" and cell AA4 would show "C". Does this involve adding the first item selected to AA2, then going back to chcek for the next selected item, and then finding the next blank cell in columnn "AA"?

    Hi all,


    I have a userform with a list of items. I would like to use the results of the selection, (one, many or all items of the list could be selected), to populate a range on a worksheet. I have found numerous articles on how to design and set up a userform but can't find anything on how to get the results of a selection output to a certain cell on a worksheet.


    Effectively I need the reulst of the selection output to a worksheet called "data" and listed from cell AA2.


    Currently I have this as the code in a command button for the user to click on after they have selected their item(s):


    Code
    For i = 1 To lstSelector.ListCount
       If lstSelector.Selected(i) Then
           Sheets("Data").Range("AA2").Value = lstSelector.Value
       End If
    Next i


    I'm sure this must be a lot easier than I'm making it, but I've got nowhere in nearly 4 hours. Any help or pointers to other sites etc., greatly appreciated.

    Hi all,


    I've changed jobs and gone back in time. Current place does not have any systems in place at all, uses Excel 2007 and has frankly baffling processses in place for even the most straightforward tasks. It's driving me crackers but that's an aside.


    I have a single dataset from which I derive a number of reports to send out to clients as invoices with backing data to support. Each of these is a single, separate workbooks with several worksheets. One of these worksheets is a pivot table and it's causing me a great many problems. I have fathomed out how to create and get the fields and dat I need I just cannot for the life of me work out how to clear the pivotcache and use the data from the current workbook. I have looked up on a great many sites to try to find the solution and have ended up going backwards and forwards as they reference each other and none of them are helping me. Therefore, at my wits end, I have come to Ozgrid. Any help greatly appreciated.


    Current code below:

    Hi,


    I have created a pivot for some data successfully in VBA. However I need to limit the results to a set month whcih I have stored in a variable. How on earth can I set the xlPageField to filter only this month? Relevant code is below I think:



    Any help greatly appreciated


    K

    I've had a look around the internet and not managed to find anything that will carry out the task I'm trying to write some code for.


    I would like to automate sending out of emails with one or more attachments to a number of addresses each month. Is there any way I could use a table in Excel with something like the following fields to enable this:


    [TABLE="width: 500"]

    [tr]


    [TD="align: center"]Code[/TD]
    [TD="align: center"]Name[/TD]
    [TD="align: center"]Email[/TD]
    [TD="align: center"]Attach1[/TD]
    [TD="align: center"]Attach2[/TD]
    [TD="align: center"]Attach3[/TD]

    [/tr]


    [tr]


    [td]

    A01

    [/td]


    [td]

    John Smith

    [/td]


    [td]

    "[email protected]"[/email]

    [/td]


    [td]

    Pathway1

    [/td]


    [td]

    Pathway2

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    A02

    [/td]


    [td]

    Fred Bloggs

    [/td]


    [td]

    "[email protected]"[/email]

    [/td]


    [td]

    Pathway1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    Each of the attachments will be named for the code so the code could be used as a lookup and where there is an attachment an email is created, populated with the title, (which could also be in the table), some narrative added and the attachment(s) added. After that I would want to be able to review, at first, so the email would need to be saved before being sent out.


    The only problem I have is that I don't really know where on earth to start. Any thoughts or recommended reading? I want it to be a loop so that I can run it to create however many emails there are in a given month. The pathway for each attachment is likely to be a combination of a name and a month, with the month housed in the sheet cell A1 or something.


    It never, ever makes much sense when I ramble on but I'm sure something like this must be possible.


    K

    Hi,


    can anyone tell me where I'm going wrong with the piece of code below:


    Code
    MsgBox "Please select a folder to save the completed files"
    Application.FileDialog(msoFileDialogFolderPicker).Show
    SvPath = CurDir & "\"


    Obviously I have declared a variable SvPath. When I run this I always end up with the files saved in the folder before the folder I click on. Any ideas where I'm going wrong?

    Re: Referencing a range using cells(x, y) in advanced filter


    Apologies. This is the full code. I had isolated the issue to that piece of code and didn't want to muddy the waters. Although I realise now that it actually makes things clearer to see the code in full.




    The last line there is commented out as it's the line I'm trying to fix and I play around with it and the line above it.

    Re: Referencing a range using cells(x, y) in advanced filter


    Thanks.


    The commented out line references range "O1" which is the header for the column I am filtering on in my 'hard coded' solution. In trying to allow myself more freedom to choose any of a number of columns to analyse I'm trying to use vCol instead of this. The range therefore needs to replicate the function of this range "O1". Is it possible that I need to reference the range as:


    Code
    Range(Cells(1,vCol),Cells(1,vCol))


    Where vCol will be an integer which is returning the value of a column in my dataset? I can't imagine that there is a genuine problem with using a variable at this point in the command. I'm sure it's just a problem with my syntax or understanding of how the AdvancedFilter function works.

    Despite my continuing problems with advanced filtering it must be my current favourite VBA technique - even though I don't really know how to use it. Consequently I've been going round in circles with this all day more or less.


    The below code is an attempt to add some generic coding to an existing solution. The commented out line works. I've commented it out so I can reinstate it as a hard coded solution if I can't figure this out.


    vCol is a variable inout by the user to tell the code which column to filter data on. I am trying to write code that allows for this to be a variable rather than something I have to manually change depending on what is being analysed from the dataset.


    If anyone can help me with why this code won't execute I'd be grateful. I think it has something to do with my syntax here .Range(.Cells(1, vCol)) but I can't figure it out.


    Code
    With Sheets("Data")
        .Range("CK2").Value = rCl.Value
        '.Range("O1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                        CriteriaRange:=.Range("CK1:CK2"), CopyToRange:=Sheets("Datasheet").Range("A33"), Unique:=False
        .Range(.Cells(1, vCol)).CurrentRegion.Columns(vCol).AdvancedFilter Action:=xlFilterCopy, _
                        CriteriaRange:=.Range("CK1:CK2"), CopyToRange:=Sheets("Datasheet").Range("A33"), Unique:=False
    End With

    I am attempting to create a suite of reports from a single dataset using VBA. The dataset needs to be cut several different ways to create reports based on a variety of fields. For example my data has fields that include customer, location, category code, product type etc.. I want to create a set of reports that cuts this data many different ways.


    For example I would need a set of reports that split the original dataset by location; where all the data is cut by location, then split by category code.


    I would also, and preferably at the same time - i.e. within the same code - need a set of reports that split the original dataset by customer; where all the data is cut by customer, then split by location.


    I would conceivably need 3 or 4 different sets of reports to be cut from the same dataset.


    I am thinking that I could get user input to populate 2 variables. The first would cut the data by x, a string variable for location, customer code etc., entered each time the user wants to run a set of reports. The second would be the variable that splits this dataset by category code or location or whatever.


    Each time the code runs I would like it to run on the full dataset, meaning that the total of the reports reconilied back to the main dataset, creating many different reports potentially.


    I'm struggling with the logistics of how to keep my loops and variables 'clean' to ensure all the data goes somewhere and I can easily reconcile back to the main dataset. I'm certain that someone, somewhere has some kind of 'fix' for this as this must be done across the world on a daily basis.


    ANy help greatly appreciated.