Export specified sheets to a new workbook using a browse box VBA. Keep the pivot data source issue.

[adrianion]

Dear community,


Context:

I am currently working on some macros that should do the following :

1. Open a (raw data file -.csv) as a new sheet in my .xlsm workbook -> Done
2. Refresh 9 pivots based on the file uploaded with point 1 (update data source of all pivots). The current setup is one pivot / worksheet -> Done

Example: Sheet1(RawData), Sheet2(pivot1), Sheet3(pivot2), Sheet4(pivot3), Sheet3(pivot4).... and so on.

3. Export PART of the refreshed pivots + the raw data by saving it in 3 different .xlsb workbooks. -> Big challenge (I need your help here)
To be more exact, I need my macro to prompt the browse window (to select the location where to save the file) + save as a new workbook containing only Sheet1(RawData),Sheet3(pivot2),Sheet3(pivot4).

Current issue:

The code that I am currently using allows me to export the data as I need to. But, when I am opening the new saved workbook, the pivots are still connected to the original file and a data refresh is required. This cannot be done by using the Refresh all button because I need to update again the data source of all pivots manually.

Is there any other way in VBA which can allow me to export a certain number of worksheets (but not all), to a location specified by me AND having the pivots in the new file connected to the correct datasource so they can be used without the need of an extra refresh?

Dim pathh As Variant

    pathh = Application.GetSaveAsFilename( _
        FileFilter:="xlWorkbookDefault Files (*.xlsb), *.xlsb", _
        Title:="Eligible and Backlog Report", _
        InitialFileName:="new report.xlsb")
    If pathh <> False Then
        ActiveWorkbook.Sheets(Array("[I]Sheet1(RawData)[/I]", "[I]Sheet2(pivot1)[/I]", _
        "[I]Sheet3(pivot4)[/I]")).Copy
        ActiveWorkbook.EnableConnections
        ActiveWorkbook.RefreshAll
        ActiveWorkbook.SaveAs pathh
        ActiveWorkbook.Close Filename:=pathh
        
        End If

Thank you in advance for your help!

Adrian