Macro for Pivottable dynamic worksheets

INOPOOL

New Member
Joined
Feb 3, 2011
Messages
43
What is the trick to record a macro that will get your Pivottable onto a new worksheet? It doesn't work after I finished recording the macro and tried to run it.

Thanks
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Show your macro. And tell in what way does it not work ( that is not much of a clue ).
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
you have posted the same question 3+ times

read the rules (#9)
- Posting guidelines, forum rules

also I posted a question you did not answer in another one of your posts
 

INOPOOL

New Member
Joined
Feb 3, 2011
Messages
43
Actually they are diff questions.

this one is about getting a pivottable onto a diff worksheet

the other one is about using the same macro for all workbooks.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

Actually they are diff questions.

this one is about getting a pivottable onto a diff worksheet

the other one is about using the same macro for all workbooks.

So, are you going to answer my queries?
 

INOPOOL

New Member
Joined
Feb 3, 2011
Messages
43
This is my code:

I placed the pivottable on the existing sheet because it wouldn't work when i try to put it on a new sheet.

Plus, i saved the macro in the personal folder, but it wouldn't work for other workbooks. Please note that the worksheet name is the same in the other wkbooks.

Sub Headcount()
'
' Headcount Macro
'
' Keyboard Shortcut: Ctrl+h
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DynamicRange", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="'Employee Roster- Active Employe'!R1C25", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Employee Roster- Active Employe").Select
Cells(1, 25).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Employee_Number"), "Count of Employee_Number", _
xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Cost_Center_(Division)")
.Orientation = xlRowField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
I answered on one of your many other's and didn't get an answer
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
It sounds like texaslynn has already given you answers.

But I don't think I can help anyway, as you are failing to give enough information ... i.e. you say "but it wouldn't work for other workbooks" ... in what way wouldn't it work? Does the macro crash? If not, what actually happens? If it does crash, what is the message, and where does the macro break? What debugging have you tried to do?
 

Forum statistics

Threads
1,141,019
Messages
5,703,763
Members
421,315
Latest member
awaisnazir139

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