Specify Selection as Range in Pivot Table Macro

[floridagunner]

Hello all,

I want to specify the active selection as the range for my pivot table but I keep getting an error message. Funny thing is this recorded code always used to work before but for some reason its not working anymore. The only change I had made in the recorded code was changing source data to selection.

I'm using the code below

Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Selection, Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable4", _
        DefaultVersion:=xlPivotTableVersion14
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    
    ActiveSheet.Move After:=Sheets(Worksheets.Count)
    ActiveSheet.Cells(3, 1).Select

Can anyone help me fix this code please ?

 

[steve the fish]

If you say something like this:

    Sh = ActiveSheet.Name
    Rng = selection.Address(ReferenceStyle:=xlR1C1)

Then the source data something like this:

SourceData:= Sh & "!" & Rng

 

[floridagunner]

Hello Steve,

I used your code modification but it still bring back an error message. The message states that;

"This command requires at least two rows of source data. You cannot use the command on a selection in only one row".

This is how I applied your modification:

    Sh = ActiveSheet.Name
    Rng = Selection.Address(ReferenceStyle:=xlR1C1)
    
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sh & "!" & Rng, Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable4", _
        DefaultVersion:=xlPivotTableVersion14

 

[steve the fish]

Sounds like the selection is the problem. What is the selection in the activesheet when the code is run?

 

[floridagunner]

It selects the entire range just as needed A1:V66285. Thats why I dont understand the error message as I definitely have all 66,285 rows selected.

Maybe its a problem of syntax like qoutes or commas ?

 

[floridagunner]

The following is the code highlighted in yellow once I press the debug button:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sh & "!" & Rng, Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable4", _
        DefaultVersion:=xlPivotTableVersion14

 

[steve the fish]

Ah ok. You need to do your declaration of Rng after you have selected the data you require. At present you have it before.

 

[steve the fish]

So:

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Rng = Selection.Address(ReferenceStyle:=xlR1C1)

 

[floridagunner]

Thank You Steve ! I switched the declarartion of range after the data selection and it worked

Thanks Again !

 

[floridagunner]

I used the following modification:

    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    
    Sh = ActiveSheet.Name
    Rng = Selection.Address(ReferenceStyle:=xlR1C1)
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sh & "!" & Rng, Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable4", _
        DefaultVersion:=xlPivotTableVersion14
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    
    ActiveSheet.Move After:=Sheets(Worksheets.Count)
    ActiveSheet.Cells(3, 1).Select