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

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:

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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

HendrixSpirit

New Member
Joined
Jan 8, 2014
Messages
3
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... :cool: Not the "Best" Fix, but it's enough for me.

Code:
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
 

Forum statistics

Threads
1,171,600
Messages
5,876,362
Members
433,194
Latest member
mamidwar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top