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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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.
Bill PrewIT / Software Engineering ConsultantCommented:
Which procedure, in which module, are you using?

CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Sandesh SAuthor Commented:
Hi Bill,

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

Thanks & Regards,

Bill PrewIT / Software Engineering ConsultantCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 PrewIT / Software Engineering ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.