Posts by Walthobum

    Re: How to save worksheets using string variables

    Thanks, I'll give this a go tomrrow. I've left the office now. For a VBA course!

    For the record I am aslo looking to create a new pathway based on the variable and also another variable, the month. Are there any significant problems creating pathways from variables that I should be aware of?

    I have created a macro that takes a dataset and creates a new workbook in a worksheet based on department codes. I now would like to save each of these worksheets as a separate workbook in a folder based on the name of the worksheet and another variable. I have yet to get to the point where I am that interested in the second variable, (which is going to be month), as I can't get the first part to work.

    The code I have loops through existing lists of variables but I am hitting a wall when it comes to the syntax used to actually copy a sheet to the desired location using the variable I have used. Here is the code I'm using.

    The error I get leaves a sheet hanging with no name and not saved.

    Re: Changing page field selection from defined range

    OK, this is where I'm at now. I have managed to create a loop, (might not impress you but I was over the moon when this worked), but I just don't have a clue how to incorporate a change of the page field to the active name in PCTList. I have commented out the code that I am currently playing with to do this, but frankly I've been going round in circles, (loops if you will), with this for a while now. Any help greatly appreciated.

    Coffee needed.


    I have managed, with some help, to work out how to set up a pivot table using VBA. Using the original data that was used to create the pivot I have created a list of all unique entries in a field. The same field that I have used as the primary page field in the pivot table. I am trying to create a loop, (or another piece of code), that will copy the sheet with the pivot table, change the page field to the unique identifier and refresh the pivot. I want the loop to create a separate sheet for each unique entry with a refreshed pivot table.

    I think I can manage the refresh and even possibly the looping mechanism but I can't find anything that illustrates how to change the page field from a defined list. In fact I'm struggling even to get a loop working in my head never mind on the sheet.

    I'm sort of here:

    Although I know that's not really a great deal of help.

    Re: Creating correct pivot table data fields in VBA

    Apologies if I have formatted this incorrectly. This is the code I've got currently. Where does the DataPivotField part go?


    I'm a relative newcomer to the wonders of VBA. Long time Excel user though.

    Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. After various attempts and online help, (thank you), I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.

    The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..

    The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.

    I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?

    Any help greatly appreciated.