Loading pivot table problem

  • Thread starter Thread starter wesley holtman
  • Start date Start date
W

wesley holtman

Hello,

I have a macro that has stopped working. The macro generates a pivot
table from a populate that is constantly changing in row and column
size. Recently the variable "PivRng" has stopped working. I have added
a watch to it and it shows that the range has only 1 row, 1 column,
and I don't understand why. The variables "FR" and "FC" are returning
the correct values. I don't get it, please help!

Sub FinNetPiv()
Dim aWB As Workbook, PivC As PivotCache, NetPop As Worksheet,
NetPiv As Worksheet, FR As Long, FC As Long, piv As PivotTable

Set aWB = ActiveWorkbook
Set NetPop = aWB.Worksheets("Nettable Pop")

'finalRow and finalCol for new sheet
FR = NetPop.Cells(Rows.Count, 8).End(xlUp).Row
FC = NetPop.Cells(1, Columns.Count).End(xlToLeft).Column

Application.ScreenUpdating = False

'add piv worksheet
Set NetPiv =
Worksheets.Add(After:=Worksheets("Nettable Pop"))
On Error Resume Next
NetPiv.Name = "Net Piv"
PROBLEM 'set range
Dim PivRng As Range
Set PivRng = NetPop.Cells(1, 1).Resize(FR, FC)

'set piv cache
Set PivC =
aWB.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=PivRng.Address)
Set piv =
PivC.CreatePivotTable(Tabledestination:=NetPiv.Range("A3"),
tablename:="FinNet")
 
If your source table is just a regular table with no other contiguous
cells then you should just be able to use:

Set PivRng = NetPop.Cells(1, 1).CurrentRegion

I have no idea why your current code is not working: if FR and FC have
the expected values then the Resize() call should be fine.

Tim
 
If your source table is just a regular table with no other contiguous
cells then you should just be able to use:

Set PivRng = NetPop.Cells(1, 1).CurrentRegion

I have no idea why your current code is not working: if FR and FC have
the expected values then the Resize() call should be fine.

Tim










- Show quoted text -
Click to expand...

Hey Tim,

Thanks for the .CurrentRegion suggestion, I am going to use that
method more often. I still can't seem to create the pivot for some
reason. Is there something wrong with my pivot cache or create pivot
table lines? Could it have something to do with not having enought
memory to hold the pivot cache?

Really confused!

Wes
 
Back
Top