Creating a pivot table using a macro

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
For some reason the text has appeared on my first post....

I have recorded a macro while creating a pivot table but the range that the original table was created from will change from week to week. I need to write the macro to select the current range on the sheet the table is being created from!

Can anyone tell me how to do this????

:rolleyes:

Code:
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "'Prior To Pivot Table'!R2C1:R1194C3", TableDestination:="", TableName:= _
        "PivotTable2"
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Proj_only" _
        , "Data")
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("GL WIP")
        .Orientation = xlDataField
        .Name = "Sum of GL WIP"
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Con Reg WIP")
        .Orientation = xlDataField
        .Name = "Sum of Con Reg WIP"
        .Function = xlSum
    End With
End Sub
 
Upvote 0
I'm not an expert but changing the first part to:


x = Selection.Address

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=x ...

worked for me.
 
Upvote 0
I think CurrentRegion will do it.

'Make Pivot Tables
Selection.CurrentRegion.Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Selection.CurrentRegion, TableDestination:="", TableName:= _
"PivotTable1"
 
Upvote 0
Thanks for the help...

I altered fairwinds code slightly...

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Prior To Pivot Table'!" & x,
As I was getting an error come up when the macro was trying to add the data!!

duplinguy - Thanks for your help also may use this in the future!
(y)
 
Upvote 0
Hi Guys I am having this same issue but my code is a little differenct to start with and the suggested work arrounds give me compile errors?

here is my code any help would be great :)

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Applicant Volume Report'!R1C1:R848C8").CreatePivotTable TableDestination:= _
"", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Vacancy Name", _
ColumnFields:="Application Stage"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Application Stage"). _
Orientation = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
 
Upvote 0
Sorry, I don't have time to get into detail but this is code I use over and over again (modified) to create pivot tables / charts with VBA.

hope it helps...

D

Code:
Sub MakeTable()
'declare the row, column, page and data field variables
Dim Pt As PivotTable
Dim PtCache As PivotCache
Dim pageField1 As String
Dim pageField2 As String
Dim pageField3 As String
Dim rowField1 As String
Dim rowField2 As String
Dim colField As String
Dim dataField As String
'delete pivot sheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'
Set s = Sheets("Data")
With Worksheets.Add
    .Name = "Pivot Sheet"
End With
    
    'Pass variable names to a String variable
    pageField1 = s.Cells(1, 2).Value  'page variable "Dispo code"
    rowField1 = s.Cells(1, 3).Value  'row field "Op No"
      
    colField1 = s.Cells(1, 10).Value  'colums field "Defect"
    dataField = s.Cells(1, 11).Value  'data to summarize "Tons"
'Name the list range
Worksheets("Data").Activate
ActiveSheet.Range("a1").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Items"
'create pivot cache
Set PtCache = ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlDatabase, _
    SourceData:=s.Range("Items")) 'entire contents of sheet
    
'create pivot table from cache
Set Pt = PtCache.CreatePivotTable( _
    TableDestination:=Sheets("Pivot Sheet").Range("A3"), _
    TableName:="QualCodeTable")
    
'add fields
With Pt
    .PivotFields(rowField1).Orientation = xlRowField   'Op No
    .PivotFields(pageField1).Orientation = xlPageField  'Dispo code
    .PivotFields(colField1).Orientation = xlColumnField  'Defect
    .PivotFields(dataField).Orientation = xlDataField  'sum of Tons
End With
Worksheets("Pivot Sheet").Columns("A:DD").AutoFit
Application.ScreenUpdating = True
'create pivot chart
'first delete chart if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot Chart").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'
    
    'add chart
    Sheets("Pivot Sheet").Activate
    Sheets("Pivot Sheet").Range("B6").Select  'selects a cell in the pivot table
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Pivot Sheet").Range("E6")  'selects a cell on the pivot sheet as the range
    ActiveChart.Location xlLocationAsNewSheet, "Pivot Chart"  'creates a new sheet named "Pivot Chart"
    'ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlColumnStacked
    
    
End Sub
 
Upvote 0
Thank you very much for your help but I am getting a compile error at this point

'Name the list range
Worksheets("Data").Activate
ActiveSheet.Range("a1").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Items"
'create pivot cache

Do I need to change anything?
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top