Selecting SourceData of a PivotTable using VBA with a Vector "i"

[HendrixSpirit]

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:

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

 

[HendrixSpirit]

I fix it using this code if anyone encounter the same problem in the future.
So this way i can select a dynamic range with "UsedRange" and it doesn't matter if i add more data to the SourceData of the PivotCache... Not the "Best" Fix, but it's enough for me.

Dim Rng As RangeSet Rng = ActiveSheet.UsedRange
ActiveWorkbook.Names.Add Name:="Range1", RefersTo:=Rng


 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Range1", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="", TableName:= _
        "Tabla dinámica1", DefaultVersion:=xlPivotTableVersion12