smithp7100
01-07-2011, 03:46 AM
Hi All,
Really hoping somebody can help me out as I have become stuck on a small part of a very large piece of code. By way of background I have written VBA code in Excel 2007, the code basically automates the creation of a number of pivot tables. In order to produce most of the code I recorded macros on the actions and then transferred that code to VBA. Everything is working correctly when all of the data conditions are met however when there is missing data for a particular time period the automation falls over.
What I believe I need is some kind of if statement but everytime I try that it doesn't work.....The VBA that works when all conditions are met is as follows;
Range("C2").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("TransactionCount"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("TransactionCount"). _
CurrentPage = "Y"
ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
CurrentPage = "201012"
The section in bold falls over when CreatedMonth is missing data as obviously it can't find 201012!, I would prefer to set this to (blank) if it can't find 201012? I have tried, and failed with the following;
'If ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
' CurrentPage = "(ALL)" Then
'ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
' CurrentPage = "201012"
'ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
' CurrentPage <> "201012" Then
' ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
' CurrentPage = "(blank)"
'End If
Any help would be hugely appreciated, maybe I need to somehow code it to move on but I tried and failed with ;
On Error Resume Next
Thanks in advance
Really hoping somebody can help me out as I have become stuck on a small part of a very large piece of code. By way of background I have written VBA code in Excel 2007, the code basically automates the creation of a number of pivot tables. In order to produce most of the code I recorded macros on the actions and then transferred that code to VBA. Everything is working correctly when all of the data conditions are met however when there is missing data for a particular time period the automation falls over.
What I believe I need is some kind of if statement but everytime I try that it doesn't work.....The VBA that works when all conditions are met is as follows;
Range("C2").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("TransactionCount"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("TransactionCount"). _
CurrentPage = "Y"
ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
CurrentPage = "201012"
The section in bold falls over when CreatedMonth is missing data as obviously it can't find 201012!, I would prefer to set this to (blank) if it can't find 201012? I have tried, and failed with the following;
'If ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
' CurrentPage = "(ALL)" Then
'ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
' CurrentPage = "201012"
'ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
' CurrentPage <> "201012" Then
' ActiveSheet.PivotTables("PivotTable2").PivotFields("CreatedMonth"). _
' CurrentPage = "(blank)"
'End If
Any help would be hugely appreciated, maybe I need to somehow code it to move on but I tried and failed with ;
On Error Resume Next
Thanks in advance