Hello all,
I really need your advice here please...
I found a vba to update a pivottable online and i'm half way to make it as i need...
however... i'm stuck on an error that i can't fix by my self...
here is the code already changed by me:
Code
Private Sub CommandButton1_Click()
Dim Data_Book As Workbook
Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Set Data_Book = Workbooks.Open(ActiveWorkbook.Path & "\" & ListBox1.Value)
Set Data_sht = Workbooks(ListBox1.Value).Worksheets("Analise 1")
Set Pivot_sht = ThisWorkbook.Worksheets("PivotTable")
PivotName = "PivotTable1"
Set DataRange = Data_sht.Range("ThisWeek")
NewRange = Data_sht.Name & "!" & DataRange.Value
If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
MsgBox "One of your data columns has a blank heading." & vbNewLine _
& "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
Exit Sub
End If
Pivot_sht.PivotTables(PivotName).ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=NewRange)
Pivot_sht.PivotTables(PivotName).RefreshTable
MsgBox PivotName & "'s data source range has been successfully updated!"
End Sub
Display More
I'm having problems here:
Can someone explain me how to fix this please???
Many thanks