macro may not be available - Problem

[schlckr7]

Hi Team,

ich hab viel nachgelesen, aber komme leider nicht weiter.

Problem:
Mein Code funktioniert wenn ich ihn manuell aus dem Editor abspiele. Wenn ich den Button klicke oder ihn über Alt+F8 abspielen will nicht. Andere Codes aus dem Modul funktionieren (Einfache Codes, die auch anders heißen)

Sub R2PIVOT()


'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long


'Insert a New Blank Worksheet
On Error Resume Next
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("1.Raw Data")


'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)


'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 1), _
TableName:="SalesPivotTable")


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")


'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Ultimate Advertiser")
.Orientation = xlRowField
.Position = 1
End With


'Insert Data Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Total Investment")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Total Investment "
End With


With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Total IG")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Total Instagram "
End With


' Define Calculated Fields
ActiveSheet.PivotTables("SalesPivotTable").CalculatedFields.Add "Share of", _
"='Total IG' /'Total Investment'", True
ActiveSheet.PivotTables("SalesPivotTable").PivotFields("BLUBB").Orientation = _
xlDataField
Columns("D:D").NumberFormat = "##%"




'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("SalesPivotTable").RepeatAllLabels xlRepeatLabels


End Sub

Habe gelesen, dass es an den variablen liegen könnte, aber mein ganzer Code ist zusammengeklaut und ich kann ihn nicht verändern

Außerdem bin ich über den Punkt: ' Define Calculated Fields noch nicht glücklich. Er funktioniert, sieht aber nicht so sauber aus.

Könnt ihr mir hier helfen?

VG
Chris

 

[silentwolf]

Hi Chris,

you should of post your question in english or put your thread into "
[h=1]Forum: Questions in Other Languages[/h]however a code is not starting by pressing Alt+F8 it either starts by pressing F5 or in steps using F8 key within your vb Editor..when you are within your Sub Procedure
How does your code look like wihin the Button Click Event?

Außerdem bin ich über den Punkt: ' Define Calculated Fields noch nicht glücklich. Er funktioniert, sieht aber nicht so sauber aus.

I am not sure about this line of codes never used anything like that.. but if it works it is all good or not?
And have you steped through your code??
If you have difficulties with english post the threat into the other forum and I reply in german.. otherwise post what you tried with the command button.. See if we can work it out.

Cheers

Albert

 

[schlckr7]

Hi Team,

Sorry for writing in German - must somehow mixed this up

Problem:
My code is working fine if I play it out of the editor. However, when I´m using the button or try opening it via F8 it doesnt work. Other codes out of this module are working fine. Easy codes

I read that it might have something to do with my variables but since I copy and paste them from different sources, I cant write them new and on my own

Sub R2PIVOT()


'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long


'Insert a New Blank Worksheet
On Error Resume Next
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("1.Raw Data")


'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)


'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 1), _
TableName:="SalesPivotTable")


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")


'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Ultimate Advertiser")
.Orientation = xlRowField
.Position = 1
End With


'Insert Data Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Total Investment")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Total Investment "
End With


With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Total IG")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Total Instagram "
End With


' Define Calculated Fields
ActiveSheet.PivotTables("SalesPivotTable").CalculatedFields.Add "Share of", _
"='Total IG' /'Total Investment'", True
ActiveSheet.PivotTables("SalesPivotTable").PivotFields("BLUBB").Orientation = _
xlDataField
Columns("D:D").NumberFormat = "##%"




'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("SalesPivotTable").RepeatAllLabels xlRepeatLabels


End Sub

@silentwolf - Thanks for your answer - even though my one was in German. I normally start my macros from the quick access toolbar but this particular macro is not working.

VG
Chris

 

[silentwolf]

Hi Chris,

well show us the code you are using to start the code from your toolbar.

 

[schlckr7]

Hi Chris,

well show us the code you are using to start the code from your toolbar.

Sorry for the confusion, there is no code.

I can either:

• Press F8 and select all macros from a list. All macros are available except the one above (greyed out)
• Click on the quick-access toolbar and select the macro from here.

The error is not linked to my button - it must be something within the code