Resize Pivot Table Data with VBA 1

[newguy86]

Hello Everyone,
I've been tasked with automating an Excel 2013 report that uses pivot tables. So far it's working except when I get to refreshing the pivot tables.

Now what happens is whenever a new report needs to be run, the data in the old report is cleared out of the Data Tab (except for the headers) and the new data that's been generated is put in it's place. Unfortunately whenever I run the following code:

Sheets("13 Week Avail Report").PivotTables("PivotTable1").RefreshTable

I get an error message that says the pivot table needs more than 1 row of data in order to refresh. This causes me to have to go in manually and resize the data for the pivot table so that it will refresh the report properly.

I've been looking around online and I can't seem to find anything that tells me how I can automatically resize the data in a pivot table using VBA so I was wondering if you guys had any ideas. Or if you think there's a better method of going about all this, then I'm all ears.

Any assistance you guys can provide will be greatly appreciated.

Travis
Charter Media

 

[SkipVought]

Make your source table a Structured Table via Insert > Tables > Table.

Reference the source in the PT, using the Table Name reference.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[newguy86]

Thanks for your response Skip!

Quick question...if I do this will it be able to accommodate to changes in data sizes? For example, the data right now is about 83k rows. But sometimes the data is less than 80k rows and other times it's over 85k rows. Will a Structured Table be able to capture just the data (ignoring any blank rows) no matter how big it is? Or is there something I would have to do differently?

Travis
Charter Media

 

[SkipVought]

Yes, no VBA required!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[newguy86]

Thank you for the suggestion. That seems to work with the exception that whenever I upload the new data through VBA the formulas in the first record of the table are now being changed from:

=SUM(F2:L2)

To something like this:

=SUM(F2:L92600)

It only happens in the first record of the table and when I look at the data it should be copying from the formula is correct. Can you think of what might be going on?

Travis
Charter Media

 

[SkipVought]

Please post the code that you just referred to.

BTW, where is the data coming from? Is the from another table? Is this is from another table, then you probably do not need the VBA code of which you speak. It may be possible to write a query to extract the data directly from that table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[newguy86]

The data comes from a separate Excel file that we use to process and reformat the raw data that comes out of our system. The formulas are put in to that file during the reformatting.

Here's the code that updates the final report:

Sub Update_Report()

Dim MainWkBk As Workbook
Dim NextWkBk As Workbook
Dim Counter As Integer
    
Set MainWkBk = ActiveWorkbook
        
    ' Delete data in Data Tab
    Sheets("Data").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
    
    ' Open files containing updated data
    For Counter = 1 To 6 Step 1
        If Counter = 1 Then
            Workbooks.Open ("G:\INVENTORY\Automation\Weekly Avails\Raw Data 13 Week\MKMA 4P MID.xlsm")
            Set NextWkBk = ActiveWorkbook
        ElseIf Counter = 2 Then
            Workbooks.Open ("G:\INVENTORY\Automation\Weekly Avails\Raw Data 13 Week\MKMA 5A MID.xlsm")
            Set NextWkBk = ActiveWorkbook
        ElseIf Counter = 3 Then
            Workbooks.Open ("G:\INVENTORY\Automation\Weekly Avails\Raw Data 13 Week\MKMA 7P 10P.xlsm")
            Set NextWkBk = ActiveWorkbook
        ElseIf Counter = 4 Then
            Workbooks.Open ("G:\INVENTORY\Automation\Weekly Avails\Raw Data 13 Week\STLO 4P MID.xlsm")
            Set NextWkBk = ActiveWorkbook
        ElseIf Counter = 5 Then
            Workbooks.Open ("G:\INVENTORY\Automation\Weekly Avails\Raw Data 13 Week\STLO 5A MID.xlsm")
            Set NextWkBk = ActiveWorkbook
        ElseIf Counter = 6 Then
            Workbooks.Open ("G:\INVENTORY\Automation\Weekly Avails\Raw Data 13 Week\STLO 7P 10P.xlsm")
            Set NextWkBk = ActiveWorkbook
        End If
        ' Copy/Paste new data into data tab
        Range("A2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        MainWkBk.Activate
        Worksheets("Data").Activate
        ActiveSheet.Paste
        Selection.End(xlDown).Select
        ActiveCell.Offset(1).Select
        NextWkBk.Activate
        ActiveWorkbook.Close False
        MainWkBk.Activate
    Next Counter
        
    ' Refresh PivotTables
    Sheets("Pre-Scheduling").PivotTables("PivotTable1").RefreshTable
    Sheets("13 Week Avail Report").PivotTables("PivotTable1").RefreshTable

    ' Save file under new name
    ActiveWorkbook.SaveAs Filename:= _
        "G:\INVENTORY\Automation\Weekly Avails\Central 13 Week Avails Report " & Format(Date, "mmddyyyy") & ".xlsm", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    

End Sub

Let me know if there's anything else you want to see.

Travis
Charter Media

 

[SkipVought]

Let me see if I understand what you are doing.

1) you delete all the DATA in the table in sheet Data, leaving HEADINGS only.

2) you loop to open 6 workbooks, copy the DATA in each and paste/append into the Data.

So where is this SUM() formula: in the Data table or in each workbook table? I cannot recreate this in my workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Actually I did manage to recreate it and have found a possible solution.

In the Data table, the SUM() formula is in the very last column in eow 2...
[tt]
=SUM([highlight #729FCF]YourTableName[/highlight][@[[highlight #729FCF]FirstFieldToSum[/highlight]]:[[highlight #729FCF]LastFieldToSum[/highlight]]])
[/tt]
When you COPY the data in the workbooks, do not include the formula, if that's where the formula resides.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[newguy86]

Correct. The formulas are in each separate workbook table and gets copied over into the Data worksheet.

I'll try to change the code to put the formulas in after I've copied the DATA over from the other files.

Travis
Charter Media

 

[SkipVought]

You don't need to do it after.

You need to do it before, in row 2 and then forget about it.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[newguy86]

Ok. I see what you're talking about.

I put everything in that you said and it works perfectly now. Thanks!

Travis
Charter Media

 

[newguy86]

I've been running this for a while now and I've noticed an interesting issue. Whenever the code is trying to pull in the first dataset from an excel spreadsheet, it takes forever to pull the data in. But after that first dataset, all the rest of the dataset's (regardless of size) get pulled in very quickly.

For example, one of my reports pulls in 6 different datasets and the first one it pulls takes about 10-15 minutes by itself to get pulled in, after that one the other 5 datasets (which are similar in size) take all together about 2-3 minutes to get pulled in.

Is there something I could do differently to help speed up this process for the first dataset?

Travis
Charter Media

 

[SkipVought]

No clue. Sorry.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}