HendrixSpirit
New Member
- Joined
- Jan 8, 2014
- Messages
- 3
I'm trying to make a Pivot Table that gets the data from a Vector created before, I need this because is part of a large macro, and the columns range changes (column = months):
Code:
And i Get Error '1004' Execution Time, Reference not valid. --> Because it doesn't read the "DataArea"
I can create the PvT using SourceData:="r1c1:r40000C16" but in that way i can't use the vector "i" that i need in case that the column row changes.
Pd: ("R1C1:R40000C16" = Range (Cells(1,1), Cells(40000, i+3)) If "i" is 13 ("i" goes from 1 to 13)
Probably is an easy fix of the code!
Jimi
Using Excel 2007
Code:
Code:
Sub Pvt_Table_with_changing_columns ()
'Vector
Range("A1").Select
i = 0
While ActiveCell.Value <> ""
i = i + 1
ActiveCell.Offset(0, 1).Select
Wend
'Selection of data area
[B]DataArea = Range (Cells(1,1), Cells(40000, i+3)[/B]
'Then I create the Pivot Table
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, [B]SourceData:= _[/B]
[B] "DataArea"[/B], Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:=Range("A17"), TableName:= _
"Pvt_NC", DefaultVersion:=xlPivotTableVersion12
End Sub
And i Get Error '1004' Execution Time, Reference not valid. --> Because it doesn't read the "DataArea"
I can create the PvT using SourceData:="r1c1:r40000C16" but in that way i can't use the vector "i" that i need in case that the column row changes.
Pd: ("R1C1:R40000C16" = Range (Cells(1,1), Cells(40000, i+3)) If "i" is 13 ("i" goes from 1 to 13)
Probably is an easy fix of the code!
Jimi
Using Excel 2007