Modify Pivot Table SourceData range

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

Manually Set your pivottable source to the SHEET level name "Database".
(you will need to create them) ex, for sheet1 it would be Sheet1!Database.
Then to refresh a new range.

change
> 'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng
to
ws.Names("Database").ReferstoRange = "=" &
SRng.Address(1,1,xlA1,True)


then after you loop to

Thisworkbook.RefreshAll

from the help on RefreshAll:

Refreshes all external data ranges and PivotTables in the workbook.

---
Generally, doing a "for each" to delete rows will fail (miss some rows)
where you have two or more successive rows which need to be deleted.

--
Regards,
Tom Ogilvy

"Mike Fogleman" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

Thanks Tom. I'll create a Named Range "Database" on each sheet and give it a
try. The blank rows thing, I normally delete from the bottom up for the
reason you mentioned, but this data will never have more than 1 blank row
adjacent to another, so I felt safe doing it in a For..Next loop.
Mike F
"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Manually Set your pivottable source to the SHEET level name "Database".
> (you will need to create them) ex, for sheet1 it would be
> Sheet1!Database.
> Then to refresh a new range.
>
> change
>> 'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng
> to
> ws.Names("Database").ReferstoRange = "=" &
> SRng.Address(1,1,xlA1,True)
>
>
> then after you loop to
>
> Thisworkbook.RefreshAll
>
> from the help on RefreshAll:
>
> Refreshes all external data ranges and PivotTables in the workbook.
>
> ---
> Generally, doing a "for each" to delete rows will fail (miss some rows)
> where you have two or more successive rows which need to be deleted.
>
> --
> Regards,
> Tom Ogilvy
>
> "Mike Fogleman" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>
>

I figured it wasn't problematic particularly given your a seasoned
developer, but thought I would mention it for the benefit of someone
pursuing the archives. <g>

--
Regards,
Tom Ogilvy

"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom. I'll create a Named Range "Database" on each sheet and give it
a
> try. The blank rows thing, I normally delete from the bottom up for the
> reason you mentioned, but this data will never have more than 1 blank row
> adjacent to another, so I felt safe doing it in a For..Next loop.
> Mike F
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > Manually Set your pivottable source to the SHEET level name "Database".
> > (you will need to create them) ex, for sheet1 it would be
> > Sheet1!Database.
> > Then to refresh a new range.
> >
> > change
> >> 'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng
> > to
> > ws.Names("Database").ReferstoRange = "=" &
> > SRng.Address(1,1,xlA1,True)
> >
> >
> > then after you loop to
> >
> > Thisworkbook.RefreshAll
> >
> > from the help on RefreshAll:
> >
> > Refreshes all external data ranges and PivotTables in the workbook.
> >
> > ---
> > Generally, doing a "for each" to delete rows will fail (miss some rows)
> > where you have two or more successive rows which need to be deleted.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Mike Fogleman" <[email protected]> wrote in message
> > news:%[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

OK, now I'm getting a Runtime Error 1004 on that last line that sets the
source range!!
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>I figured it wasn't problematic particularly given your a seasoned
> developer, but thought I would mention it for the benefit of someone
> pursuing the archives. <g>
>
> --
> Regards,
> Tom Ogilvy
>
> "Mike Fogleman" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks Tom. I'll create a Named Range "Database" on each sheet and give
>> it
> a
>> try. The blank rows thing, I normally delete from the bottom up for the
>> reason you mentioned, but this data will never have more than 1 blank row
>> adjacent to another, so I felt safe doing it in a For..Next loop.
>> Mike F
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Manually Set your pivottable source to the SHEET level name "Database".
>> > (you will need to create them) ex, for sheet1 it would be
>> > Sheet1!Database.
>> > Then to refresh a new range.
>> >
>> > change
>> >> 'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng
>> > to
>> > ws.Names("Database").ReferstoRange = "=" &
>> > SRng.Address(1,1,xlA1,True)
>> >
>> >
>> > then after you loop to
>> >
>> > Thisworkbook.RefreshAll
>> >
>> > from the help on RefreshAll:
>> >
>> > Refreshes all external data ranges and PivotTables in the workbook.
>> >
>> > ---
>> > Generally, doing a "for each" to delete rows will fail (miss some
>> > rows)
>> > where you have two or more successive rows which need to be deleted.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "Mike Fogleman" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

You shouldn't use that line.

Since the pivot table is set to use the sheet level name Database, all you
need to do is redefine the range that database refers to.

--
Regards,
Tom Ogilvy

"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> OK, now I'm getting a Runtime Error 1004 on that last line that sets the
> source range!!
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> >I figured it wasn't problematic particularly given your a seasoned
> > developer, but thought I would mention it for the benefit of someone
> > pursuing the archives. <g>
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Mike Fogleman" <[email protected]> wrote in message
> > news:[email protected]...
> >> Thanks Tom. I'll create a Named Range "Database" on each sheet and give
> >> it
> > a
> >> try. The blank rows thing, I normally delete from the bottom up for the
> >> reason you mentioned, but this data will never have more than 1 blank
row
> >> adjacent to another, so I felt safe doing it in a For..Next loop.
> >> Mike F
> >> "Tom Ogilvy" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Manually Set your pivottable source to the SHEET level name
"Database".
> >> > (you will need to create them) ex, for sheet1 it would be
> >> > Sheet1!Database.
> >> > Then to refresh a new range.
> >> >
> >> > change
> >> >> 'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng
> >> > to
> >> > ws.Names("Database").ReferstoRange = "=" &
> >> > SRng.Address(1,1,xlA1,True)
> >> >
> >> >
> >> > then after you loop to
> >> >
> >> > Thisworkbook.RefreshAll
> >> >
> >> > from the help on RefreshAll:
> >> >
> >> > Refreshes all external data ranges and PivotTables in the workbook.
> >> >
> >> > ---
> >> > Generally, doing a "for each" to delete rows will fail (miss some
> >> > rows)
> >> > where you have two or more successive rows which need to be deleted.
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> > "Mike Fogleman" <[email protected]> wrote in message
> >> > news:%[email protected]...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

It is the line you gave me to use instead of the one that I had. However,
using the JWalk Name Lister add-in, I found only one named range, the last
one I had created, and it was Workbook level, not worksheet level. I guess I
was not creating the named ranges correctly. Each time I created the name
Database on a sheet, it was replacing the one before it.
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> You shouldn't use that line.
>
> Since the pivot table is set to use the sheet level name Database, all you
> need to do is redefine the range that database refers to.
>
> --
> Regards,
> Tom Ogilvy
>
> "Mike Fogleman" <[email protected]> wrote in message
> news:[email protected]...
>> OK, now I'm getting a Runtime Error 1004 on that last line that sets the
>> source range!!
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> >I figured it wasn't problematic particularly given your a seasoned
>> > developer, but thought I would mention it for the benefit of someone
>> > pursuing the archives. <g>
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "Mike Fogleman" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Thanks Tom. I'll create a Named Range "Database" on each sheet and
>> >> give
>> >> it
>> > a
>> >> try. The blank rows thing, I normally delete from the bottom up for
>> >> the
>> >> reason you mentioned, but this data will never have more than 1 blank
> row
>> >> adjacent to another, so I felt safe doing it in a For..Next loop.
>> >> Mike F
>> >> "Tom Ogilvy" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > Manually Set your pivottable source to the SHEET level name
> "Database".
>> >> > (you will need to create them) ex, for sheet1 it would be
>> >> > Sheet1!Database.
>> >> > Then to refresh a new range.
>> >> >
>> >> > change
>> >> >> 'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng
>> >> > to
>> >> > ws.Names("Database").ReferstoRange = "=" &
>> >> > SRng.Address(1,1,xlA1,True)
>> >> >
>> >> >
>> >> > then after you loop to
>> >> >
>> >> > Thisworkbook.RefreshAll
>> >> >
>> >> > from the help on RefreshAll:
>> >> >
>> >> > Refreshes all external data ranges and PivotTables in the workbook.
>> >> >
>> >> > ---
>> >> > Generally, doing a "for each" to delete rows will fail (miss some
>> >> > rows)
>> >> > where you have two or more successive rows which need to be deleted.
>> >> >
>> >> > --
>> >> > Regards,
>> >> > Tom Ogilvy
>> >> >
>> >> > "Mike Fogleman" <[email protected]> wrote in message
>> >> > news:%[email protected]...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

If all else fails, re-read instructions. I am now creating Sheet level
range names as you instructed. Will let you know how the code works when I
get this done.
"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> It is the line you gave me to use instead of the one that I had. However,
> using the JWalk Name Lister add-in, I found only one named range, the last
> one I had created, and it was Workbook level, not worksheet level. I guess
> I was not creating the named ranges correctly. Each time I created the
> name Database on a sheet, it was replacing the one before it.
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
>> You shouldn't use that line.
>>
>> Since the pivot table is set to use the sheet level name Database, all
>> you
>> need to do is redefine the range that database refers to.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "Mike Fogleman" <[email protected]> wrote in message
>> news:[email protected]...
>>> OK, now I'm getting a Runtime Error 1004 on that last line that sets the
>>> source range!!
>>> "Tom Ogilvy" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >I figured it wasn't problematic particularly given your a seasoned
>>> > developer, but thought I would mention it for the benefit of someone
>>> > pursuing the archives. <g>
>>> >
>>> > --
>>> > Regards,
>>> > Tom Ogilvy
>>> >
>>> > "Mike Fogleman" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> Thanks Tom. I'll create a Named Range "Database" on each sheet and
>>> >> give
>>> >> it
>>> > a
>>> >> try. The blank rows thing, I normally delete from the bottom up for
>>> >> the
>>> >> reason you mentioned, but this data will never have more than 1 blank
>> row
>>> >> adjacent to another, so I felt safe doing it in a For..Next loop.
>>> >> Mike F
>>> >> "Tom Ogilvy" <[email protected]> wrote in message
>>> >> news:%[email protected]...
>>> >> > Manually Set your pivottable source to the SHEET level name
>> "Database".
>>> >> > (you will need to create them) ex, for sheet1 it would be
>>> >> > Sheet1!Database.
>>> >> > Then to refresh a new range.
>>> >> >
>>> >> > change
>>> >> >> 'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng
>>> >> > to
>>> >> > ws.Names("Database").ReferstoRange = "=" &
>>> >> > SRng.Address(1,1,xlA1,True)
>>> >> >
>>> >> >
>>> >> > then after you loop to
>>> >> >
>>> >> > Thisworkbook.RefreshAll
>>> >> >
>>> >> > from the help on RefreshAll:
>>> >> >
>>> >> > Refreshes all external data ranges and PivotTables in the workbook.
>>> >> >
>>> >> > ---
>>> >> > Generally, doing a "for each" to delete rows will fail (miss some
>>> >> > rows)
>>> >> > where you have two or more successive rows which need to be
>>> >> > deleted.
>>> >> >
>>> >> > --
>>> >> > Regards,
>>> >> > Tom Ogilvy
>>> >> >
>>> >> > "Mike Fogleman" <[email protected]> wrote in message
>>> >> > news:%[email protected]...
>>> >> >> 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
>>> >> >>
>>> >> >>
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

Tom, the line of code you gave me:

ws.Names("Database").ReferstoRange = "=" &
SRng.Address(1,1,xlA1,True)

changes the entire range of data to 0's and brings up the Circular Reference
toolbar. Now what?

"Mike Fogleman" <[email protected]> wrote in message
news:%[email protected]...
> If all else fails, re-read instructions. I am now creating Sheet level
> range names as you instructed. Will let you know how the code works when I
> get this done.
> "Mike Fogleman" <[email protected]> wrote in message
> news:[email protected]...
>> It is the line you gave me to use instead of the one that I had. However,
>> using the JWalk Name Lister add-in, I found only one named range, the
>> last one I had created, and it was Workbook level, not worksheet level. I
>> guess I was not creating the named ranges correctly. Each time I created
>> the name Database on a sheet, it was replacing the one before it.
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>>> You shouldn't use that line.
>>>
>>> Since the pivot table is set to use the sheet level name Database, all
>>> you
>>> need to do is redefine the range that database refers to.
>>>
>>> --
>>> Regards,
>>> Tom Ogilvy
>>>
>>> "Mike Fogleman" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> OK, now I'm getting a Runtime Error 1004 on that last line that sets
>>>> the
>>>> source range!!
>>>> "Tom Ogilvy" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>> >I figured it wasn't problematic particularly given your a seasoned
>>>> > developer, but thought I would mention it for the benefit of someone
>>>> > pursuing the archives. <g>
>>>> >
>>>> > --
>>>> > Regards,
>>>> > Tom Ogilvy
>>>> >
>>>> > "Mike Fogleman" <[email protected]> wrote in message
>>>> > news:[email protected]...
>>>> >> Thanks Tom. I'll create a Named Range "Database" on each sheet and
>>>> >> give
>>>> >> it
>>>> > a
>>>> >> try. The blank rows thing, I normally delete from the bottom up for
>>>> >> the
>>>> >> reason you mentioned, but this data will never have more than 1
>>>> >> blank
>>> row
>>>> >> adjacent to another, so I felt safe doing it in a For..Next loop.
>>>> >> Mike F
>>>> >> "Tom Ogilvy" <[email protected]> wrote in message
>>>> >> news:%[email protected]...
>>>> >> > Manually Set your pivottable source to the SHEET level name
>>> "Database".
>>>> >> > (you will need to create them) ex, for sheet1 it would be
>>>> >> > Sheet1!Database.
>>>> >> > Then to refresh a new range.
>>>> >> >
>>>> >> > change
>>>> >> >> 'ws.PivotTableWizard SourceType:=xlDatabase, SourceData:=SRng
>>>> >> > to
>>>> >> > ws.Names("Database").ReferstoRange = "=" &
>>>> >> > SRng.Address(1,1,xlA1,True)
>>>> >> >
>>>> >> >
>>>> >> > then after you loop to
>>>> >> >
>>>> >> > Thisworkbook.RefreshAll
>>>> >> >
>>>> >> > from the help on RefreshAll:
>>>> >> >
>>>> >> > Refreshes all external data ranges and PivotTables in the
>>>> >> > workbook.
>>>> >> >
>>>> >> > ---
>>>> >> > Generally, doing a "for each" to delete rows will fail (miss some
>>>> >> > rows)
>>>> >> > where you have two or more successive rows which need to be
>>>> >> > deleted.
>>>> >> >
>>>> >> > --
>>>> >> > Regards,
>>>> >> > Tom Ogilvy
>>>> >> >
>>>> >> > "Mike Fogleman" <[email protected]> wrote in message
>>>> >> > news:%[email protected]...
>>>> >> >> 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
>>>> >> >>
>>>> >> >>
>>>> >> >
>>>> >> >
>>>> >>
>>>> >>
>>>> >
>>>> >
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Sorry, I had tested it and copied the wrong line of code. Should be using
the refers to property, not the referstorange property.

ws.Names("Database").Refersto = "=" &
SRng.Address(1,1,xlA1,True)

--
Regards,
Tom Ogilvy


"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> Tom, the line of code you gave me:
>
> ws.Names("Database").ReferstoRange = "=" &
> SRng.Address(1,1,xlA1,True)
>
> changes the entire range of data to 0's and brings up the Circular
Reference
> toolbar. Now what?
>
> "Mike Fogleman" <[email protected]> wrote in message
> news:%[email protected]...
> > If all else fails, re-read instructions. I am now creating Sheet level
> > range names as you instructed. Will let you know how the code works when
I
> > get this done.
> > "Mike Fogleman" <[email protected]> wrote in message
> > news:[email protected]...
> >> It is the line you gave me to use instead of the one that I had.
However,
> >> using the JWalk Name Lister add-in, I found only one named range, the
> >> last one I had created, and it was Workbook level, not worksheet level.
I
> >> guess I was not creating the named ranges correctly. Each time I
created
> >> the name Database on a sheet, it was replacing the one before it.
> >> "Tom Ogilvy" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> You shouldn't use that line.
> >>>
> >>> Since the pivot table is set to use the sheet level name Database, all
> >>> you
> >>> need to do is redefine the range that database refers to.
> >>>
> >>> --
> >>> Regards,
> >>> Tom Ogilvy
> >>>
> >>> "Mike Fogleman" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>>> OK, now I'm getting a Runtime Error 1004 on that last line that sets
> >>>> the
> >>>> source range!!
> >>>> "Tom Ogilvy" <[email protected]> wrote in message
> >>>> news:[email protected]...
> >>>> >I figured it wasn't problematic particularly given your a seasoned
> >>>> > developer, but thought I would mention it for the benefit of
someone
> >>>> > pursuing the archives. <g>
> >>>> >
> >>>> > --
> >>>> > Regards,
> >>>> > Tom Ogilvy
> >>>> >
> >>>> > "Mike Fogleman" <[email protected]> wrote in message
> >>>> > news:[email protected]...
> >>>> >> Thanks Tom. I'll create a Named Range "Database" on each sheet and
> >>>> >> give
> >>>> >> it
> >>>> > a
> >>>> >> try. The blank rows thing, I normally delete from the bottom up
for
> >>>> >> the
> >>>> >> reason you mentioned, but this data will never have more than 1
> >>>> >> blank
> >>> row
> >>>> >> adjacent to another, so I felt safe doing it in a For..Next loop.
> >>>> >> Mike F
> >>>> >> "Tom Ogilvy" <[email protected]> wrote in message
> >>>> >> news:%[email protected]...
> >>>> >> > Manually Set your pivottable source to the SHEET level name
> >>> "Database".
> >>>> >> > (you will need to create them) ex, for sheet1 it would be
> >>>> >> > Sheet1!Database.
> >>>> >> > Then to refresh a new range.
> >>>> >> >
> >>>> >> > change
> >>>> >> >> 'ws.PivotTableWizard SourceType:=xlDatabase,
SourceData:=SRng
> >>>> >> > to
> >>>> >> > ws.Names("Database").ReferstoRange = "=" &
> >>>> >> > SRng.Address(1,1,xlA1,True)
> >>>> >> >
> >>>> >> >
> >>>> >> > then after you loop to
> >>>> >> >
> >>>> >> > Thisworkbook.RefreshAll
> >>>> >> >
> >>>> >> > from the help on RefreshAll:
> >>>> >> >
> >>>> >> > Refreshes all external data ranges and PivotTables in the
> >>>> >> > workbook.
> >>>> >> >
> >>>> >> > ---
> >>>> >> > Generally, doing a "for each" to delete rows will fail (miss
some
> >>>> >> > rows)
> >>>> >> > where you have two or more successive rows which need to be
> >>>> >> > deleted.
> >>>> >> >
> >>>> >> > --
> >>>> >> > Regards,
> >>>> >> > Tom Ogilvy
> >>>> >> >
> >>>> >> > "Mike Fogleman" <[email protected]> wrote in message
> >>>> >> > news:%[email protected]...
> >>>> >> >> 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
> >>>> >> >>
> >>>> >> >>
> >>>> >> >
> >>>> >> >
> >>>> >>
> >>>> >>
> >>>> >
> >>>> >
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>

OK, thanks. That seems to be working.
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Sorry, I had tested it and copied the wrong line of code. Should be using
> the refers to property, not the referstorange property.
>
> ws.Names("Database").Refersto = "=" &
> SRng.Address(1,1,xlA1,True)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Mike Fogleman" <[email protected]> wrote in message
> news:[email protected]...
>> Tom, the line of code you gave me:
>>
>> ws.Names("Database").ReferstoRange = "=" &
>> SRng.Address(1,1,xlA1,True)
>>
>> changes the entire range of data to 0's and brings up the Circular
> Reference
>> toolbar. Now what?
>>
>> "Mike Fogleman" <[email protected]> wrote in message
>> news:%[email protected]...
>> > If all else fails, re-read instructions. I am now creating Sheet level
>> > range names as you instructed. Will let you know how the code works
>> > when
> I
>> > get this done.
>> > "Mike Fogleman" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> It is the line you gave me to use instead of the one that I had.
> However,
>> >> using the JWalk Name Lister add-in, I found only one named range, the
>> >> last one I had created, and it was Workbook level, not worksheet
>> >> level.
> I
>> >> guess I was not creating the named ranges correctly. Each time I
> created
>> >> the name Database on a sheet, it was replacing the one before it.
>> >> "Tom Ogilvy" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>> You shouldn't use that line.
>> >>>
>> >>> Since the pivot table is set to use the sheet level name Database,
>> >>> all
>> >>> you
>> >>> need to do is redefine the range that database refers to.
>> >>>
>> >>> --
>> >>> Regards,
>> >>> Tom Ogilvy
>> >>>
>> >>> "Mike Fogleman" <[email protected]> wrote in message
>> >>> news:[email protected]...
>> >>>> OK, now I'm getting a Runtime Error 1004 on that last line that sets
>> >>>> the
>> >>>> source range!!
>> >>>> "Tom Ogilvy" <[email protected]> wrote in message
>> >>>> news:[email protected]...
>> >>>> >I figured it wasn't problematic particularly given your a seasoned
>> >>>> > developer, but thought I would mention it for the benefit of
> someone
>> >>>> > pursuing the archives. <g>
>> >>>> >
>> >>>> > --
>> >>>> > Regards,
>> >>>> > Tom Ogilvy
>> >>>> >
>> >>>> > "Mike Fogleman" <[email protected]> wrote in message
>> >>>> > news:[email protected]...
>> >>>> >> Thanks Tom. I'll create a Named Range "Database" on each sheet
>> >>>> >> and
>> >>>> >> give
>> >>>> >> it
>> >>>> > a
>> >>>> >> try. The blank rows thing, I normally delete from the bottom up
> for
>> >>>> >> the
>> >>>> >> reason you mentioned, but this data will never have more than 1
>> >>>> >> blank
>> >>> row
>> >>>> >> adjacent to another, so I felt safe doing it in a For..Next loop.
>> >>>> >> Mike F
>> >>>> >> "Tom Ogilvy" <[email protected]> wrote in message
>> >>>> >> news:%[email protected]...
>> >>>> >> > Manually Set your pivottable source to the SHEET level name
>> >>> "Database".
>> >>>> >> > (you will need to create them) ex, for sheet1 it would be
>> >>>> >> > Sheet1!Database.
>> >>>> >> > Then to refresh a new range.
>> >>>> >> >
>> >>>> >> > change
>> >>>> >> >> 'ws.PivotTableWizard SourceType:=xlDatabase,
> SourceData:=SRng
>> >>>> >> > to
>> >>>> >> > ws.Names("Database").ReferstoRange = "=" &
>> >>>> >> > SRng.Address(1,1,xlA1,True)
>> >>>> >> >
>> >>>> >> >
>> >>>> >> > then after you loop to
>> >>>> >> >
>> >>>> >> > Thisworkbook.RefreshAll
>> >>>> >> >
>> >>>> >> > from the help on RefreshAll:
>> >>>> >> >
>> >>>> >> > Refreshes all external data ranges and PivotTables in the
>> >>>> >> > workbook.
>> >>>> >> >
>> >>>> >> > ---
>> >>>> >> > Generally, doing a "for each" to delete rows will fail (miss
> some
>> >>>> >> > rows)
>> >>>> >> > where you have two or more successive rows which need to be
>> >>>> >> > deleted.
>> >>>> >> >
>> >>>> >> > --
>> >>>> >> > Regards,
>> >>>> >> > Tom Ogilvy
>> >>>> >> >
>> >>>> >> > "Mike Fogleman" <[email protected]> wrote in message
>> >>>> >> > news:%[email protected]...
>> >>>> >> >> 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
>> >>>> >> >>
>> >>>> >> >>
>> >>>> >> >
>> >>>> >> >
>> >>>> >>
>> >>>> >>
>> >>>> >
>> >>>> >
>> >>>>
>> >>>>
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

I tried the solution,decriped in http://www.contextures.com/xlPivot01.html but the Pivot Table is giving me an "Invalid Reference" Error.

Any idea what I could be doing wrong?