Excel macro Add-in duplicate spreadsheet pop up

My macro creates a pivot table from scratch from a set data dump. I am trying to move this macro to an add-in. the add-in works on the new data each time, but for some reason it pops up a second workbook that my code originally worked on.

I've read through add-in websites to make sure I set up the add-in correctly. My other add-in macro works (only have 2. still learning)

Sub OpenAndHoldPivot()

    Dim sht As Worksheet
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable
    Dim StartPvt As String
    Dim SrcData As String

'Determine the data range you want to pivot

    Dim finRow As String
    With ActiveWorkbook
    finRow = ActiveSheet.Range("A200000").End(xlUp).Row
    SrcData = ActiveSheet.Name & "!" & Range("A4:BO" & finRow - 1).Address  (ReferenceStyle:=xlR1C1)
    End With

'Create a new worksheet

    Set sht = Sheets.Add

'Pivot Table Start

    StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

'Create Pivot Cache from Source Data

     Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
     SourceType:=xlDatabase, _
     SourceData:=SrcData)

'Create Pivot table from Pivot Cache

     Set pvt = pvtCache.CreatePivotTable( _
     TableDestination:=StartPvt, _
     TableName:="PivotTable1")

'------------------------------------------------------------------------------


    Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Add item to the Report Filter

    pvt.PivotFields("Future Fill Date").Orientation = xlPageField

'Add item to the Column Labels

    pvt.PivotFields("Worker Type").Orientation = xlColumnField

'Add item to the Row Labels

    pvt.PivotFields("Flex Division").Orientation = xlRowField

'Turn on Automatic updates/calculations --like screenupdating to speed up code

    pvt.ManualUpdate = False

'------------------------------------------------------------------------------


    ActiveSheet.Name = "Pivot"


'------------------------------------------------------------------------------

    Dim pf As String
    Dim pf_Name As String

    pf = "FT/PT"
    pf_Name = "Sum of FT/PT"

    Set pvt = ActiveSheet.PivotTables("PivotTable1")

    pvt.AddDataField pvt.PivotFields("FT/PT"), pf_Name, xlCount

'------------------------------------------------------------------------------

    Dim pm As PivotField

    Set pm = ActiveSheet.PivotTables("PivotTable1").PivotFields("Future Fill Date")

'Clear Out Any Previous Filtering

    pm.ClearAllFilters

'Filter on 2014 items

    pm.CurrentPage = "(blank)"
'------------------------------------------------------------------------------


    Sheets("Sheet1").Name = "Data"


End Sub

Any ideas on what I am doing wrong?

Solution

I really think your problem lies with some inconsistent refernces (or lack of refernces) to which workbook or worksheet you're using. Specifically, I believe the problem is with the line

Set sht = Sheets.Add

Since your Sheets reference doesn't specify which workbook to add the new worksheet, it will default to the currently active workbook which could be your add-in workbook. You'll help yourself a great deal if you become much clearer about which workbooks and worksheets you want. To illustrate this using your example, you can start with

Sub OpenAndHoldPivot()
    Dim workingWB As Workbook
    Dim workingWS As Worksheet
    Set workingWB = ActiveWorkbook
    Set workingWS = activeworksheet

    'Determine the data range you want to pivot
    Dim srcData As Range
    Dim srcDataText As String
    With workingWS
        Dim finRow As Long
        finRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set srcData = .Range("A4").Resize(finRow - 1, 67)
        srcDataText = .Name & "!" & srcData.Address(ReferenceStyle:=xlR1C1)
    End With

This establishes clearly which workbook all your code will operate. Also, if you take a look at my With block and compare it to yours, you can see you missed a . before the Range reference, which again will likely refer back to either your add-in or the active workbook (and you can never be too sure.

After that, I just continue down the code...

    'Create a new worksheet in the working workbook
    Dim pivotWS As Worksheet
    Set pivotWS = workingWB.Sheets.Add

    'Pivot Table Start
    Dim StartPvtText As String
    StartPvtText = pivotWS.Name & "!" & pivotWS.Range("A3").Address(ReferenceStyle:=xlR1C1)

    'Create Pivot Cache from Source Data
    Dim pvtCache As PivotCache
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
                   SourceType:=xlDatabase, _
                   SourceData:=srcDataText)

    'Create Pivot table from Pivot Cache
    Dim pvt As PivotTable
    Set pvt = pvtCache.CreatePivotTable( _
              TableDestination:=StartPvtText, _
              TableName:="PivotTable1")

Notice also that I declare all my variables as close to where they're used as possible. This makes it much easier to follow and to be certain you're using the correct variable with the intended type.

Further on down your code, you've referred to the ActiveSheet a few times. Replace that with a specific reference in order to be consistent. In my code, I rarely use ActiveSheet or ActiveCell. I attempted to fix the references below here in the full module, but only you can tell if this is accurate (because it's not perfectly clear which book or sheet you want).

Finally, there's the very last line of code Sheets("Sheet1").Name = "Data". I have no idea which workbook that should reference, but my guess is that it should be workingWB.Sheets("Sheet1").Name = "Data".

Option Explicit

Sub OpenAndHoldPivot()
    Dim workingWB As Workbook
    Dim workingWS As Worksheet
    Set workingWB = ActiveWorkbook
    Set workingWS = activeworksheet

    'Determine the data range you want to pivot
    Dim srcData As Range
    Dim srcDataText As String
    With workingWS
        Dim finRow As Long
        finRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set srcData = .Range("A4").Resize(finRow - 1, 67)
        srcDataText = .Name & "!" & srcData.Address(ReferenceStyle:=xlR1C1)
    End With

    'Create a new worksheet in the working workbook
    Dim pivotWS As Worksheet
    Set pivotWS = workingWB.Sheets.Add

    'Pivot Table Start
    Dim StartPvtText As String
    StartPvtText = pivotWS.Name & "!" & pivotWS.Range("A3").Address(ReferenceStyle:=xlR1C1)

    'Create Pivot Cache from Source Data
    Dim pvtCache As PivotCache
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
                   SourceType:=xlDatabase, _
                   SourceData:=srcDataText)

    'Create Pivot table from Pivot Cache
    Dim pvt As PivotTable
    Set pvt = pvtCache.CreatePivotTable( _
              TableDestination:=StartPvtText, _
              TableName:="PivotTable1")

    '------------------------------------------------------------------------------
    Set pvt = pivotWS.PivotTables("PivotTable1")

    'Add item to the Report Filter
    pvt.PivotFields("Future Fill Date").Orientation = xlPageField

    'Add item to the Column Labels
    pvt.PivotFields("Worker Type").Orientation = xlColumnField

    'Add item to the Row Labels
    pvt.PivotFields("Flex Division").Orientation = xlRowField

    'Turn on Automatic updates/calculations --like screenupdating to speed up code
    pvt.ManualUpdate = False

    '------------------------------------------------------------------------------
    pivotWS.Name = "Pivot"

    '------------------------------------------------------------------------------
    Dim pf As String
    Dim pf_Name As String
    pf = "FT/PT"
    pf_Name = "Sum of FT/PT"
    Set pvt = pivotWS.PivotTables("PivotTable1")
    pvt.AddDataField pvt.PivotFields("FT/PT"), pf_Name, xlCount

    '------------------------------------------------------------------------------
    Dim pm As PivotField
    Set pm = pivotWS.PivotTables("PivotTable1").PivotFields("Future Fill Date")

    'Clear Out Any Previous Filtering
    pm.ClearAllFilters

    'Filter on 2014 items
    pm.CurrentPage = "(blank)"

    '------------------------------------------------------------------------------
    workingWB.Sheets("Sheet1").Name = "Data"
End Sub