VBA Code To Add PivotTable To Existing Sheet

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:

sheets.add
Sheets("Sheet3").Select

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:

sheets.add
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

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")

Sub AddNamedTab(sNameToUse As String)
Dim iReportIteration As Integer
Dim bTabNamed


sheets.add


On Error GoTo NameInUse


Do Until bTabNamed
    If iReportIteration = 0 Then
        ActiveSheet.Name = sNameToUse
        bTabNamed = True
    Else
        Sheets(sNameToUse).Name = sNameToUse & iReportIteration
        ActiveSheet.Name = sNameToUse
        bTabNamed = True
    End If
NotNamed:
Loop


On Error GoTo 0


Exit Sub


NameInUse:
iReportIteration = iReportIteration + 1
Resume NotNamed:


End Sub

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;

Sub AddSheet()
Dim ws As Worksheet


On Error Resume Next
Set ws = Sheets("Data")
On Error GoTo 0


    If ws Is Nothing Then
       Sheets.Add().Name = "Data"
    Else
       Sheets("Data").UsedRange.Clear
    End If


End Sub

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

Re: VBA Code To Add PivotTable To Existing Sheet

I was just being paranoid about the tab limit it seems, if it exists, it's larger than 10,000,