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.Ran ge("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

On Nov 14, 7:22*am, wesley holtman wrote:
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.Ran ge("A3"),
tablename:="FinNet")

On Nov 14, 4:02*pm, Tim Williams wrote:
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

On Nov 14, 7:22*am, wesley holtman wrote:



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.Ran ge("A3"),
tablename:="FinNet")- Hide quoted text -

- Show quoted text -

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

Which exact line is failing, and what is the error you're getting?

Tim


On Nov 15, 7:08*am, wesley holtman wrote:
On Nov 14, 4:02*pm, Tim Williams wrote:





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

On Nov 14, 7:22*am, wesley holtman wrote:

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.Ran ge("A3"),
tablename:="FinNet")- Hide quoted text -

- Show quoted text -

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- Hide quoted text -

- Show quoted text -