Need to create Pivot table with macro and account for varying range sizes

[kcleere]

Greetings,

I am able to create a pivot table with a recorded macro but I do not know how to edit the code so that it automatically adjusts to the data range size as it varies from one sheet to another. Below I'm posting the part of the manually recorded VBA that "I think" establishes the initial data range and I'm hoping someone can tell me how to edit it to work as needed. Thank you.



'Sub Pivot()
'
' Pivot Macro
'
' Keyboard Shortcut: Ctrl+w
'
' Sheets.Add
' ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
' "Export (9)!R1C1:R172C7", Version:=6).CreatePivotTable TableDestination:= _
' "Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=6
' Sheets("Sheet1").Select
' Cells(3, 1).Select
' With ActiveSheet.PivotTables("PivotTable1")

 

[Domenic]

You can use CurrentRegion.Address...

SourceData:=Worksheets("Export (9)").Range("A1").CurrentRegion.Address)

However, here's another way to create your pivot table...

[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] Pivot()

    [COLOR=darkblue]Dim[/COLOR] PCache [COLOR=darkblue]As[/COLOR] PivotCache
    [COLOR=darkblue]Dim[/COLOR] PT [COLOR=darkblue]As[/COLOR] PivotTable
    
    [COLOR=green]'Create a pivot cache[/COLOR]
    [COLOR=darkblue]Set[/COLOR] PCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("Export (9)").Range("A1").CurrentRegion.Address)
    
    [COLOR=green]'Add a new worksheet[/COLOR]
    Worksheets.Add
    
    [COLOR=green]'Create the pivot table from the cache[/COLOR]
    [COLOR=darkblue]Set[/COLOR] PT = ActiveSheet.PivotTables.Add( _
        PivotCache:=PCache, _
        TableDestination:=Range("A1"), _
        TableName:="PivotTable1")
        
    [COLOR=darkblue]With[/COLOR] PT
        [COLOR=green]'Add fields[/COLOR]
        '
        [COLOR=green]'[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

 

[kcleere]

Thank you. Initially I was not able to make these work. I'll try again when I have more time.

 

[Domenic]

Are you getting an error? If so, where does the error occur? And what type of error do you get?

 

[kcleere]

Using your second option to create a pivot...this is the section that fails. It "could" be that it specifies a sheet (export) name and the sheet name can vary?

'Create a pivot cache
' Set PCache = ActiveWorkbook.PivotCaches.Create( _
' SourceType:=xlDatabase, _
' SourceData:=Worksheets("Export").Range("A1").CurrentRegion.Address)
'
'Add a new worksheet

 

[Domenic]

If you make sure that the sheet containing the source data is the active sheet, you can refer to the active sheet

[COLOR=#333333]SourceData:=ActiveSheet.Range("A1").CurrentRegion.Address[/COLOR]

Or, simply...

[COLOR=#333333]SourceData:=Range("A1").CurrentRegion.Address[/COLOR]

Of course, when the new worksheet is added, it becomes the active sheet.

 

[kcleere]

Thank you...I'll give that a try