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 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,021
Messages
5,856,849
Members
431,837
Latest member
megantang

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