Opening a new file with pivot table data in it

[Nelsini]

Hello!

As the tittle says, I want to open a new file with data from the original file. In the original file we have a pivot table (Daily_report_tbl) with 6 columns and we need to copy the first 3 into the new file. The row number varies from day to day so I can't just copy from row x to row y.
The copied data doesn't need to be turned into a table, I just need the values.

Here is the code I have so far:

Sub createFile ()
    Dim reportPath As String
    
    reportPath = ActiveWorkbook.Path & "\" & "2.Emails" & "\"
    
    Dim newWB As Workbook
    Set newWB = Workbooks.Add
    
    newWB.SaveAs reportPath & Format(Now, "yyyy-mm-dd") & "_dummyfile.xlsx"
    
End Sub

I don't really know how to incorporate the copy and paste of the table since I can't find anything online..

Any help is appreciated and thanks in advance!!!

 

[GraH]

The same could be achieved with Power Query, saved in a template file.
Short description (UI driven):
- data - from file - from excel
- select file then select the sheet where the pivot is
- maybe remove TOP rows (to start where you'd have titles)
- select columns to keep (remove other columns)
- filter out "nulls" on one of the columns (end of range).
- load as table

Save this file as xltx and confirm this message.

Double click to create a new file based on the template.
Save as is a manual step in this case (though you can save a macro file too using xltm.)

 

[Nelsini]

Well this is a file with several macros and we can't really remove any lines in the file. Context:

This is a pivot table that gathers info on 3 to 5 different excel files that are retrieved daily from Splunk. We have several macros assigned to different buttons and one of them is to create the new xlsx file that we use to input the values into a unix script. We copy the values manually from the first excel to the new one, and I wanted this to be automatically done when opening the new excel.

Sorry for any typos and I appreciate the help!!

 

[GraH]

Hi, context always matters. I'm just thinking the button could call the template (and not make it).

 

[Nelsini]

Well I'd prefer a VBA solution but I'll take a look at creating excel templates since I didn't know that was a thing. Thank you for all the help!

(Still looking for a VBA solution if anyone can provide one!! :D)

 

[GraH]

I tried to help ... At least you are now looking into something that might be of use: templates.
Maybe this can help you further (and potentially some other threads over here as well if you look for VBA for dynamic range)

5 Ways to Create A Dynamic Auto-Adjusting VBA Range

A huge turning point for me when I was teaching myself how to write VBA was being able to create dynamic code. This meant I didn't have to go into my macros and change range addresses in my code every time the size of my data changed (which was typically every day).

www.thespreadsheetguru.com

 

[Nelsini]

I tried to help ... At least you are now looking into something that might be of use: templates.
Maybe this can help you further (and potentially some other threads over here as well if you look for VBA for dynamic range)

5 Ways to Create A Dynamic Auto-Adjusting VBA Range

A huge turning point for me when I was teaching myself how to write VBA was being able to create dynamic code. This meant I didn't have to go into my macros and change range addresses in my code every time the size of my data changed (which was typically every day).

www.thespreadsheetguru.com

Well, looking into templates, I can't really seem to get the template table to stay filtered (we filter the original table by day since it's a daily proccedure). Any thoughts?

 

[GraH]

Nothing on top of my mind. When I make templates, it never holds data to be filtered.
Since you're using macro's, can't the macro set the correct filter?

Do take note I'm not using VBA anymore since about 10 years. Can't help in that area.

 

[Nelsini]

Well I tried to set up a macro to do so, but since I don't really understand VBA that much we're just giving up on the ideia of automatically pasting the data. It's only a couple of clicks anyways, no one will die due to 15 seconds of copy and paste haha but thanks anyways!!