I have 10 worksheets of data each with a pivot table(1) on them. New data is
added each month to each sheet. My routine loops through each sheet, cleans
out blank rows and copies the new data to a master sheet "Data". How do I
modify the new data range for the pivot tables? My last line of code creates
a new pivot table, which is not what I want of course. And then finally to
refresh the tables. Does ActiveWorkbook.RefreshAll work on pivot tables
also?

Sub CleanUp()
Dim ws As Worksheet
Dim DRow As Long, SRow As Long
Dim DRng As Range, SRng As Range
Dim c As Range, shrng As Range
Dim i As Integer

DRow = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
If DRow < 3 Then DRow = 3
Set DRng = Worksheets("Data").Range("A3:G" & DRow)
DRng.Delete

For i = 3 To 12
Set ws = Worksheets(i)
SRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
Set shrng = ws.Range("B3:B" & SRow)
For Each c In shrng
If c.Value = "" Then ws.Range(c.Offset(0, -1), c.Offset(0,
4)).Delete
Next c
SRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set SRng = ws.Range("A3:E" & SRow)
DRow = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
If DRow < 2 Then DRow = 2
Set DRng = Worksheets("Data").Range("B" & DRow + 1)
SRng.Copy DRng
DRow = Worksheets("Data").Cells(Rows.Count, "B").End(xlUp).Row
Set DRng = Worksheets("Data").Range("A3:A" & DRow)
DRng.Value = ws.Name
Set SRng = ws.Range("A1:F" & SRow)
'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng

Next i
End Sub