VBA Code To Add PivotTable To Existing Sheet

  • I have a macro which creates the pivot table, copies and pastes the entire pivot table as values and formats in columns "J" thru "R'.

    I then delete columns "A" thru "I" and have values which I can use.

    My problem is the next day I update my data sheet, click on the macro and it creats another spreadsheet.

    I deleted out the previous days sheet but it still identifies the new pivot table worksheet as the next sequincial sheet number.

    I need a way to keep the same name on the new pivot table worksheet.


  • Re: Keep The Same Tab Name For Pivot Table

    John can you post your code so we can see where it is named. Easier to provide a change to code if the code is available first.

  • Re: Keep The Same Tab Name For Pivot Table

    From the sound of it, you probably have a recorded macro that does something like:


    And it breaks. Since when you add a sheet, the default name (Sheet1,2,3) always iterates, even if you delete sheets.

    If you have to add a tab and then refer back to it by it's index name, then rename it after it's created, like this:

    activesheet.name = "Report"

    And then refer to it later using the name you've chosen.

    Adding and deleting tabs isn't usually best practice (neither is referring to sheets by their index name), but this might work for your immediate need. You could have a procedure that names each tab to the first available default name "Sheet1,2,3" etc, but that would certainly be going about it the wrong way around.

  • Re: Keep The Same Tab Name For Pivot Table

    As you can see from the code, I Recorded the macro by:

    Selecting the pivot table range,
    Placed the fields where I wanted them,
    Formated the chart,
    Filterd out blanks in Print field
    copied to the range to the right and posted values and formats
    deleted the existing pivot table info from the spreadsheet
    leaving me usable data

    But when I look it has added another spreadsheet.

    The attached example is a little different than my first description. This only has 5 columns


  • Re: Keep The Same Tab Name For Pivot Table

    Yeah. Looks like on the last line of "NEWSHEET_Click()" you could rename it, if you want the displayed name to be the same every day:

    activesheet.name = "Report" 'or whatever you want to call it

    But that will throw an error if you ever forget to delete the tab. This is the sort of thing that recorded macros are pretty horrible at.

    Edit: Took pity, and though it's probably encouraging bad coding, I wrote you a sub that will always name it what you want, and iterate the name of the old tab. Enjoy :)

    Put this code into a standard code module and instead of sheets.add put:

    AddNamedTab ("NameToUse")

    Second edit: I'm not sure about this, but I was thinking about it and I think Excel has a hard limit to how many sheets you can EVER add to a single workbook, even if you delete them (something in the back of my mind says I saw this happen once, but I may be wrong), I'll have to check around to be sure. If that's the case, adding tabs every day will basically corrupt your workbook eventually, and is a bad idea.

  • Re: Keep The Same Tab Name For Pivot Table

    Try something like;

    Then record a Macro adding your PivotTable to the sheet "Data".

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!