It
will then apply that data range to the PivotTable??™s SourceData property, and then refresh the
PivotTable.
In the VBE, create a new subroutine and name it RefreshPivotTableFromWorksheet. Add
the following code:
Sub RefreshPivotTableFromWorksheet()
Dim sData As String
Dim iWhere As Integer
Dim rngData As Range
sData = ActiveSheet.PivotTables("PivotTable1").SourceData
iWhere = InStr(1, sData, "!")
sData = Left(sData, iWhere)
Set rngData = ??
ActiveWorkbook.Sheets(Left(sData, iWhere - 1)).Cells(1, 1).CurrentRegion
ActiveSheet.PivotTables("PivotTable1").SourceData = ??
sData & rngData.Address(, , xlR1C1)
End Sub
Let??™s take a look at what this code is doing. We have three variables declared. sData will
hold the value of the current range for the PivotTable??™s source data. We want to find the bang
character (!) so we can retrieve the name of the worksheet the data came from. We??™ll store that
in the iWhere variable. And finally, we have a variable of type Range, rngData, that will be
assigned the CurrentRegion of cell A1 on the data worksheet. With this information, we have
the tools to refresh our pivot data any time detail data is added on the data worksheet.
The first step is to get the current data source for the PivotTable:
sData = ActiveSheet.PivotTables("PivotTable1").SourceData
Next we??™ll find the ! character:
iWhere = InStr(1, sData, "!")
Now we want the worksheet name including the !:
sData = Left(sData, iWhere)
CHAPTER 6 n PIVOTTABLES 236
We modify sData because we only needed it to determine the worksheet name.
Pages:
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231