Re: Modify Pivot Table SourceData range

Tech-Archive recommends: Speed Up your PC by fixing your registry

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 work*** level. I guess I
was not creating the named ranges correctly. Each time I created the name
Database on a ***, it was replacing the one before it.
"Tom Ogilvy" <twogilvy@xxxxxxx> wrote in message
news:OAAAJjLYGHA.3972@xxxxxxxxxxxxxxxxxxxxxxx
You shouldn't use that line.

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

--
Regards,
Tom Ogilvy

"Mike Fogleman" <mikefogleman@xxxxxxxxxxxxx> wrote in message
news:edOynGLYGHA.3840@xxxxxxxxxxxxxxxxxxxxxxx
OK, now I'm getting a Runtime Error 1004 on that last line that sets the
source range!!
"Tom Ogilvy" <twogilvy@xxxxxxx> wrote in message
news:uaocH8KYGHA.4120@xxxxxxxxxxxxxxxxxxxxxxx
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" <mikefogleman@xxxxxxxxxxxxx> wrote in message
news:ejB6guKYGHA.3704@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Tom. I'll create a Named Range "Database" on each *** 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" <twogilvy@xxxxxxx> wrote in message
news:%23VsO9NKYGHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
Manually Set your pivottable source to the *** 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" <mikefogleman@xxxxxxxxxxxxx> wrote in message
news:%23zjnhjJYGHA.4688@xxxxxxxxxxxxxxxxxxxxxxx
I have 10 worksheets of data each with a pivot table(1) on them.
New
data
is
added each month to each ***. My routine loops through each
***,
cleans
out blank rows and copies the new data to a master *** "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 Work***
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














.

Quantcast