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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,959
Messages
5,627,861
Members
416,278
Latest member
CellHell

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