VBA Code to create pivot in other excel file using other file's data

I have a file named A in which I have VBA coding to create pivot in file B using data or source data for pivot in file B itself. I am able to create pivot in same file but not in other bfile as said above. Please help
Sandesh SAsked:
Who is Participating?
Bill PrewConnect With a Mentor Commented:
This should get you pretty close, if not all the way there.

Sub createPivotTableExistingSheet()

    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    Dim Rng As Range
    Dim wbMacro As Workbook
    Dim wbPivot As Workbook

    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String

    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable

    Set wbMacro = ThisWorkbook
    Set wbPivot = Workbooks.Open("D:\Excel\OT.xlsx")

    'identify source and destination worksheets
    With wbPivot
        Set mySourceWorksheet = .Worksheets("Data")
        Set myDestinationWorksheet = .Worksheets("PivotTable")
        myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)

        'identify row and column numbers that define source data cell range
        myFirstRow = 5
        myLastRow = 1034
        myFirstColumn = 1
        myLastColumn = 6

        'obtain address of source data cell range
        With mySourceWorksheet.Cells
            mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
        End With

        'create Pivot Table cache and create Pivot Table report based on that cache
        Set myPivotTable = wbPivot.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")

        'add, organize and format Pivot Table fields
        With myPivotTable
            .PivotFields("Item").Orientation = xlRowField
            With .PivotFields("Units Sold")
                .Orientation = xlDataField
                .Position = 1
                .Function = xlSum
                    .NumberFormat = "#,##0.00"
            End With

            With .PivotFields("Sales Amount")
                .Orientation = xlDataField
                .Position = 2
                .Function = xlSum
                    .NumberFormat = "#,##0.00"
            End With

            With ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5")
                .Orientation = xlPageField
                .Position = 1
            End With

            Dim ws As Worksheet
            Dim pt As PivotTable
            Dim pi As PivotItem
            Dim pf As Variant
            Dim ArrayofStatestoInclude(3) As String
            Dim i As Integer
            Dim found As Boolean

            ArrayofStatestoInclude(0) = "Arkansas"
            ArrayofStatestoInclude(1) = "Texas"
            ArrayofStatestoInclude(2) = "New York"

            Application.ScreenUpdating = False

            'Make a selection change on a Pivot Table's Pivot Field to a new Pivot Item
            Set pt = wbPivot.Sheets("PivotTable").PivotTables(1)

            'Change Territory to Houston
            'First, set the 3 levels to (ALL) just in case - to ensure a match

            For Each pf In pt.PageFields
                pf.CurrentPage = "(ALL)"
            Next pf

            'Now, set the PivotItem to focus on Houston on the Territory Pagefield
            For Each pf In pt.PageFields
                If pf.Name = "Col5" Then
                    For Each pi In pf.PivotItems
                        With pi
                            For i = 0 To UBound(ArrayofStatestoInclude)
                                If pi.Name = ArrayofStatestoInclude(i) Then found = True
                            Next i

                            If Not found Then
                                pi.Visible = False
                            End If

                        End With
                    Next pi
                End If
            Next pf

            'Set PivotFields = ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5")

            '.PivotFields.Pivotfileters.Add xlCaptionEquals, Value1:="105"
            '.PivotFields.Add xlCaptionEquals, Value1:="105"

            '        ActiveSheet.PivotTables.ManualUpdate = True

            '        ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5"). _
            multiplepageitems = True

            'ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5").CurrentPage = _

            'ActiveSheet.Range("B1") = "105"

            '        .PivotFields.Pivotfileters.Add xlCaptionEquals, Value1:="105"
            '.PivotFields.Add xlCaptionEquals, Value1:="105"

            'ActiveSheet.PivotTables("PivotTable2").PivotFields("Col5").CurrentPage = _

            'ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5").CurrentPage = _
            "105, 106"

            'ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5").PivotFilters. _
            Add Type: = xlCaptionEquals, Value1: = "105"

            'Dim pf As PivotField, pi As PivotItem

            'Set pf = ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5")
            'pf.EnableMultiplePageItems = True

            'For Each pi In pf.PivotItems
            'pi.Visible = Not IsError(Application.Match(pi.Caption, Range("H5:H6"), 0))

            'Next pi

            'ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5").CurrentPage = _
            'With ActiveSheet.PivotTables("PivotTableExistingSheet").PivotFields("Col5")
            '.PivotItems("105").Visible = True
            '.PivotItems("106").Visible = True

        End With

        'Dim PvtTbl As PivotTable
        'Set PvtTbl = Worksheets("PivotTable").PivotTables("PivotTableExistingSheet")

        'PvtTbl.PivotFields("Col5").PivotFilters.Add Type:=xlCaptionContains, Value1:="108"

        'Dim pvt As PivotTable
        'Dim pvtField As PivotField

        'Set pvt = ActiveSheet.PivotTables("PivotTableExistingSheet")
        'Set pvtField = pvt.PivotFiles("Col5")

        'pvtField.PivotFilters.Add xlCaptionEquals, Value1:="105"

        'ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
        'Application.ScreenUpdating = True

    End With

End Sub

Open in new window

Bill PrewCommented:
Need to see your VBA code, and a sample of the two files would help.

Sandesh SAuthor Commented:
Hi Bill,

Apologies for delay and Thanks for your response.

Attached are the files wherein i have to create pivot in file OT. SPP is the file wherein i have the code and OT is the file wherein i have to create pivot using VBA coding. VBA code is working fine in file SPP if i try to create pivot in SPP file itself, but i want it to i.e coding in SPP to create pivot in file OT by using data in file OT itself, the same way as now it is doing in SPP.

Thank you in advance.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Bill PrewCommented:
Which procedure, in which module, are you using?

Sandesh SAuthor Commented:
Hi Bill,

I am using Sub createPivotTableExistingSheet() in module 1. Please ignore module2 and 3

Thanks & Regards,

Sandesh SAuthor Commented:
Hi Bill,

It is working perfectly fine.

I am thankful to you that you helped me at the right time. I really appreciate for all you have done to help me. I just want to say fabulous for your effort. I think you don't mind if come up with any other challenging situations like this.

Thanks again...!

Bill PrewCommented:
Welcome, always glad to help.

Sandesh SAuthor Commented:
I would like to thank Bill Prew for helping to fix this. Keep up the spirit. Thanks, Sandesh
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.