If Then Else Pivot table
 

Where am I going wrong?

I am looking to have an If Then Else statement evaluate if cell A2 in a
specified tab is blank; if it is blank, the tab should be deleted, if it is
not, a pivot table based on the tab should be created in a new tab. Here is
the code that I have:

Sheets("Jan").Select
If Range("A2") < "" Then

Sheets.Add.Name = "JANP"

Dim wsdJANP As Worksheet
Dim ptcacheJANP As PivotCache
Dim ptJANP As PivotTable
Dim prangeJANP As Range
Dim finalrowJANP As Long
Set wsdJANP = Worksheets("Jan")

For Each ptJANP In wsd.PivotTables
ptJANP.TableRange2.Clear
Next ptJANP

finalrowJANP = wsdJANP.Cells(65536, 1).End(xlUp).Row
Set prangeJANP = wsdJANP.Cells(1, 1).Resize(finalrowJANP, 28)
Set ptcacheJANP = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:=prangeJANP.Address)
Set ptJANP =
ptcacheJANP.CreatePivotTable(tabledestination:=She ets("JANP").Range("A1"),
tablename:="PivotTable1")
pt.ManualUpdate = True

ptJANP.AddFields RowFields:=Array("ECPD_PROFILE_NM")
With ptJANP.PivotFields("ACTIVATIONS")
..Orientation = xlDataField
..Function = xlSum
..Position = 1

Else
Sheets("Jan").Delete
End If

When I attempt this code I get an error message that states "Compile Error:
Else without If"

If Then Else Pivot table
 

You're missing the End With that should be just above the Else line.


joemeshuggah wrote:
Where am I going wrong?

I am looking to have an If Then Else statement evaluate if cell A2 in a
specified tab is blank; if it is blank, the tab should be deleted, if it is
not, a pivot table based on the tab should be created in a new tab. Here is
the code that I have:

Sheets("Jan").Select
If Range("A2") < "" Then

Sheets.Add.Name = "JANP"

Dim wsdJANP As Worksheet
Dim ptcacheJANP As PivotCache
Dim ptJANP As PivotTable
Dim prangeJANP As Range
Dim finalrowJANP As Long
Set wsdJANP = Worksheets("Jan")

For Each ptJANP In wsd.PivotTables
ptJANP.TableRange2.Clear
Next ptJANP

finalrowJANP = wsdJANP.Cells(65536, 1).End(xlUp).Row
Set prangeJANP = wsdJANP.Cells(1, 1).Resize(finalrowJANP, 28)
Set ptcacheJANP = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:=prangeJANP.Address)
Set ptJANP =
ptcacheJANP.CreatePivotTable(tabledestination:=She ets("JANP").Range("A1"),
tablename:="PivotTable1")
pt.ManualUpdate = True

ptJANP.AddFields RowFields:=Array("ECPD_PROFILE_NM")
With ptJANP.PivotFields("ACTIVATIONS")
.Orientation = xlDataField
.Function = xlSum
.Position = 1

Else
Sheets("Jan").Delete
End If

When I attempt this code I get an error message that states "Compile Error:
Else without If"




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com