Find bottom of dataset and refresh pivot

[rachel06]

Hi!

I'm sure this question has been asked before, but I've searched and worded it different ways and coming up blank.

I'm trying to make one of the templates my team uses as easy and quick as possible. One of the things we have to do is paste in a data extract (all will have the same columns (13) but different number of rows) and refresh a pivot that's on a different sheet. The "shell" of the pivot table is already created. Right now, we go through the steps of changing the data source and refreshing, but it'd be nice to be able to just click a button and have it done, especially if some people aren't super Excel savvy.

How do I do this with VBA? I I'm just having a hard time with the bit to find the bottom of the data each time.

I'm sure it's super simple. Thanks so much!!!

 

[Domenic]

The following assumptions have been made...

1) The workbook running the code contains three worksheets, one for the PivotTable, one for the data set, and one for the new data set.

2) The worksheet called "New Data" contains the new data to be pasted at the bottom of the data set.

3) The worksheet called "Data" contains the data set.

4) The worksheet called "Pivot" contains the PivotTable.

Here's the code...

Option Explicit

Sub Update_PivotTable()

    Dim dataWorksheet As Worksheet
    Set dataWorksheet = ThisWorkbook.Worksheets("Data")

    Dim nextRow As Long
    With dataWorksheet
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
        
    ThisWorkbook.Worksheets("New Data").Range("A1").CurrentRegion.Copy Destination:=dataWorksheet.Cells(nextRow, "A")
    
    ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache _
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataWorksheet.Range("A1").CurrentRegion)
    
End Sub

Hope this helps!

 

[rachel06]

The following assumptions have been made...

1) The workbook running the code contains three worksheets, one for the PivotTable, one for the data set, and one for the new data set.

2) The worksheet called "New Data" contains the new data to be pasted at the bottom of the data set.

3) The worksheet called "Data" contains the data set.

4) The worksheet called "Pivot" contains the PivotTable.

Here's the code...

Option Explicit

Sub Update_PivotTable()

    Dim dataWorksheet As Worksheet
    Set dataWorksheet = ThisWorkbook.Worksheets("Data")

    Dim nextRow As Long
    With dataWorksheet
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
       
    ThisWorkbook.Worksheets("New Data").Range("A1").CurrentRegion.Copy Destination:=dataWorksheet.Cells(nextRow, "A")
   
    ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1").ChangePivotCache _
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataWorksheet.Range("A1").CurrentRegion)
   
End Sub

Hope this helps!

Hi! I'll go through this, but there are only two worksheets in this:

1. The sheet where the data will be pasted. This will always be blank when the worksheet is open besides the header row.
2. The sheet where the pivot table is. I currently have a the text "Please paste data here" in cell A2 so I can at least have the shell of the pivot table available in that sheet.