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

Sandesh S
Sandesh S used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

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


»bp

Author

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.
SPP.xlsm
OT.xlsx
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Which procedure, in which module, are you using?


»bp
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Author

Commented:
Hi Bill,

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

Thanks & Regards,

Sandesh
Test your restores, not your backups...
Top Expert 2016
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 = _
            "105"

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

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

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

            '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.ClearAllFilters
            '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 = _
            "(All)"
            '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.ClearAllFilters

        '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


»bp

Author

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...!

Sandesh
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Welcome, always glad to help.


»bp

Author

Commented:
I would like to thank Bill Prew for helping to fix this. Keep up the spirit. Thanks, Sandesh

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial