Styjun

How deep is the liquid in a half-full hemisphere?

Why is STARTTLS still used?

As a team leader is it appropriate to bring in fundraiser candy?

After viewing logs with journalctl, how do I exit the screen that says "lines 1-2/2 (END)"?

What can Thomas Cook customers who have not yet departed do now it has stopped operating?

Discrepancy regarding AoE point of origin between English and German PHB

Can I build a half bath without permits?

Create the same subfolders in another folder

"until mine is on tight" is a idiom?

If a spaceship ran out of fuel somewhere in space between Earth and Mars, does it slowly drift off to the Sun?

How much horsepower to weight is required for a 1:1 thrust ratio

I transpose the source code, you transpose the input!

Why aren't faces sharp in my f/1.8 portraits even though I'm carefully using center-point autofocus?

Installation of ImageMagick software fails with "configure: error: libltdl is required"

Is there a relationship between prime numbers and music?

お仕事に学校頑張って meaning

Convert a string of digits from words to an integer

Calculate the Ultraradical

Dynamic DataSource for Droplist in Content Editor

"I will not" or "I don't" as an answer for negative orders?

Is population size a parameter, or sample size a statistic?

Avoiding dust scattering when you drill

Received a package but didn't order it

Smallest PRIME containing the first 11 primes as sub-strings

If all required fields in the Pivot Table are not there or not in the right order, then display MsgBox in Excel VBA

Is there a way to crack the password on an Excel VBA Project?How to avoid using Select in Excel VBAHow to add custom calculated fields to a pivot table (Excel 2010).Check for values other than '1' in excel column and display MsgBoxHow to consolidate multiple sheets into a pivot table in Excel 2013Excel VBA to filter Pivot Table and Pivot Chart for previous day - Pivot Filter FieldUSE Vba to Iterate through Pivot Table while copying single field into separate sheetExtract data underlying Excel pivot tables with many recordsPivot table top 10 filter is displaying more than 10 itemUnable to Collapse Pivot Table Hierarchy Connected to SSAS Tabular Model

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

My power pivot table has Rows, Values, and Filters. They all need to have specific values for each and in a specific order. If they are not, I want a MsgBox to display that that field is incorrectly placed or absent. I tried recording a macro by adding each item to try and start the code:

'Add Rows
With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Accounts].[Account Hierarchy]")
 .Orientation = xlRowField
 .Position = 1
End With

With ActiveSheet.PivotTables("Invoices").CubeFields("[Oracle Inv].[Supplier]")
 .Orientation = xlRowField
 .Position = 2
End With

'Add Values
ActiveSheet.PivotTables("Invoices").AddDataField ActiveSheet.PivotTables( _
 "Invoices").CubeFields("[Measures].[Sum of Invoice Amount]"), _
 "Invoice Amount"

ActiveSheet.PivotTables("Invoices").AddDataField ActiveSheet.PivotTables( _
 "Invoices").CubeFields("[Measures].[Sum of Distrib Amount]"), _
 "Distrib Amount"

'Add Filters
 With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Calendar].[Date Hierarchy]")
 .Orientation = xlPageField
 .Position = 1
End With
With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Companies1].[Region Description]")
 .Orientation = xlPageField
 .Position = 2

But I don't know how to continue...

excel vba

share|improve this question

asked Mar 28 at 19:16

JadeJade

1666 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hey Jade, you can use If Not... Then... MsgBox("Your text here") statements to check if the rows, values or filters are set to their desired value.You can combine these using the logical operator And to make sure all conditions are met. Secondly I highly recommend defining your table at the top of your sub as it will shorten it significantly. I.e. Dim pvt As PivotTable Set pvt = ActiveSheet.PivotTables("PivotTable1"). This will allow you to adress the table in your with blocks by it's name. I.e. pvt.CubeFields( _ "[Accounts].[Account Hierarchy]") .Orientation = xlRowField
  
  – Nick
  Mar 29 at 6:20
  
  

  
  
  
  

add a comment
 | 

0

My power pivot table has Rows, Values, and Filters. They all need to have specific values for each and in a specific order. If they are not, I want a MsgBox to display that that field is incorrectly placed or absent. I tried recording a macro by adding each item to try and start the code:

'Add Rows
With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Accounts].[Account Hierarchy]")
 .Orientation = xlRowField
 .Position = 1
End With

With ActiveSheet.PivotTables("Invoices").CubeFields("[Oracle Inv].[Supplier]")
 .Orientation = xlRowField
 .Position = 2
End With

'Add Values
ActiveSheet.PivotTables("Invoices").AddDataField ActiveSheet.PivotTables( _
 "Invoices").CubeFields("[Measures].[Sum of Invoice Amount]"), _
 "Invoice Amount"

ActiveSheet.PivotTables("Invoices").AddDataField ActiveSheet.PivotTables( _
 "Invoices").CubeFields("[Measures].[Sum of Distrib Amount]"), _
 "Distrib Amount"

'Add Filters
 With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Calendar].[Date Hierarchy]")
 .Orientation = xlPageField
 .Position = 1
End With
With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Companies1].[Region Description]")
 .Orientation = xlPageField
 .Position = 2

But I don't know how to continue...

excel vba

share|improve this question

asked Mar 28 at 19:16

JadeJade

1666 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hey Jade, you can use If Not... Then... MsgBox("Your text here") statements to check if the rows, values or filters are set to their desired value.You can combine these using the logical operator And to make sure all conditions are met. Secondly I highly recommend defining your table at the top of your sub as it will shorten it significantly. I.e. Dim pvt As PivotTable Set pvt = ActiveSheet.PivotTables("PivotTable1"). This will allow you to adress the table in your with blocks by it's name. I.e. pvt.CubeFields( _ "[Accounts].[Account Hierarchy]") .Orientation = xlRowField
  
  – Nick
  Mar 29 at 6:20
  
  

  
  
  
  

add a comment
 | 

My power pivot table has Rows, Values, and Filters. They all need to have specific values for each and in a specific order. If they are not, I want a MsgBox to display that that field is incorrectly placed or absent. I tried recording a macro by adding each item to try and start the code:

'Add Rows
With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Accounts].[Account Hierarchy]")
 .Orientation = xlRowField
 .Position = 1
End With

With ActiveSheet.PivotTables("Invoices").CubeFields("[Oracle Inv].[Supplier]")
 .Orientation = xlRowField
 .Position = 2
End With

'Add Values
ActiveSheet.PivotTables("Invoices").AddDataField ActiveSheet.PivotTables( _
 "Invoices").CubeFields("[Measures].[Sum of Invoice Amount]"), _
 "Invoice Amount"

ActiveSheet.PivotTables("Invoices").AddDataField ActiveSheet.PivotTables( _
 "Invoices").CubeFields("[Measures].[Sum of Distrib Amount]"), _
 "Distrib Amount"

'Add Filters
 With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Calendar].[Date Hierarchy]")
 .Orientation = xlPageField
 .Position = 1
End With
With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Companies1].[Region Description]")
 .Orientation = xlPageField
 .Position = 2

But I don't know how to continue...

excel vba

share|improve this question

asked Mar 28 at 19:16

JadeJade

1666 bronze badges

'Add Rows
With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Accounts].[Account Hierarchy]")
 .Orientation = xlRowField
 .Position = 1
End With

With ActiveSheet.PivotTables("Invoices").CubeFields("[Oracle Inv].[Supplier]")
 .Orientation = xlRowField
 .Position = 2
End With

'Add Values
ActiveSheet.PivotTables("Invoices").AddDataField ActiveSheet.PivotTables( _
 "Invoices").CubeFields("[Measures].[Sum of Invoice Amount]"), _
 "Invoice Amount"

ActiveSheet.PivotTables("Invoices").AddDataField ActiveSheet.PivotTables( _
 "Invoices").CubeFields("[Measures].[Sum of Distrib Amount]"), _
 "Distrib Amount"

'Add Filters
 With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Calendar].[Date Hierarchy]")
 .Orientation = xlPageField
 .Position = 1
End With
With ActiveSheet.PivotTables("Invoices").CubeFields( _
 "[Companies1].[Region Description]")
 .Orientation = xlPageField
 .Position = 2

share|improve this question

asked Mar 28 at 19:16

JadeJade

1666 bronze badges

share|improve this question

asked Mar 28 at 19:16

JadeJade

1666 bronze badges

asked Mar 28 at 19:16

JadeJade

1666 bronze badges

asked Mar 28 at 19:16

JadeJade

1666 bronze badges