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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
OK, now I'm getting a Runtime Error 1004 on that last line that sets
the
source range!!
"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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