Find bottom of dataset and refresh pivot

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
126
Office Version
  1. 365
Platform
  1. Windows
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!!!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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...

VBA 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!
 
Upvote 1
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...

VBA 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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top