PDA

View Full Version : VBA Code (Excel) - Please Help



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

Bob Phillips
01-07-2011, 04:15 AM
Can you post the workbook with your code already embedded?

smithp7100
01-07-2011, 04:25 AM
Hope this helps

Bob Phillips
01-07-2011, 05:10 AM
I meant the Excel workbook, with pivots and VBA code.

smithp7100
01-07-2011, 05:14 AM
Unfortunately the file is 5mb so will not allow the upload!!