Copy filtered pivot table data to new sheets instead of new workbooks

[Martin sherk]

I've come so far in my project and it all depends on solving my last issue, the below code helps me create a Pivot table for all my customers based on company code, so it filters company code criteria in the pivot table and then copies the filtered data to new workbooks named after the company code.

All I need is that instead of copying data to the new workbook, I want it to be copied to the current workbook (the workbook with the pivot table on), just adding new sheets named after the company code and copy the filtered data in it.

Wish someone would help me with this, I would be so thnakful.

My code:

Option Explicit
Sub GetAllEmployeeSelections()

    'Const filePath As String = "C:\Users\User\Desktop\" 'save location for new files

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pvt As PivotTable


    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("PivotTable")
    Set pvt = ws.PivotTables("PivotTable1")
pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone

    Application.ScreenUpdating = False

    Dim pvtField As PivotField
    Dim item As Long
    Dim item2 As Long

    Set pvtField = pvt.PivotFields("Company Code")

    For item = 1 To pvtField.PivotItems.Count

          pvtField.PivotItems(item).Visible = True

          For item2 = 1 To pvtField.PivotItems.Count

              If item2 <> item Then pvtField.PivotItems(item2).Visible = False

          Next item2

        Dim newBook As Workbook
        Set newBook = Workbooks.Add

        With newBook

            Dim currentName As String
            currentName = pvtField.PivotItems(item).Name

            .Worksheets(1).Name = currentName

            pvt.TableRange2.Copy

            Worksheets(currentName).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

        End With

    Next item

    Application.ScreenUpdating = True

End Sub

 

[Alex Blakenburg]

I'm afraid you need to post that as a new thread.

Assuming your company name is USA1, I would be naming the pivot sheet "USA1 PVT"
That way when the pivot's are created it can just add PVT to the company name.
Then store the company name and copy all the sheets starting with the Company Name ie USA1 to a new workbook.

I would definitely not be copying the whole Pivot and would go back to copying it as Values only.

 

[Martin sherk]

I'm afraid you need to post that as a new thread.

Assuming your company name is USA1, I would be naming the pivot sheet "USA1 PVT"
That way when the pivot's are created it can just add PVT to the company name.
Then store the company name and copy all the sheets starting with the Company Name ie USA1 to a new workbook.

I would definitely not be copying the whole Pivot and would go back to copying it as Values only.

Yes, sir, I've just created a completely new thread, I would appreciate it if you can check it out.

thanks!