macro comment used in 2003 excel not working in 2010

[jawahars]

Hi

i use the below comment to create the pivot in 2003 excel but it is not worling in 2010 vision kindly help.

Sheets("base").Select
rn1 = Cells.Find(What:="*", after:=Range("a1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

Dim myrange1 As Range
Set myrange1 = Sheets("base").Range("A1:H" & rn1)

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
myrange1).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("GL Amount"), "Sum of GL Amount", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of GL Amount")
.NumberFormat = "0.00_);(0.00)"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch #")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GL Code")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch #").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveWorkbook.ShowPivotTableFieldList = False

[HansV]

Do you get an error message? If so, what does it say and on which line does it occur?

[jawahars]

the Error is Run time ERROR 13

Type MISMATCH

Yellow highlited line is below:-

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
myrange1).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10

[Rudi]

Give this version a try...

Code: Select all

    Sheets("base").Select
    rn1 = Cells.Find(What:="*", after:=Range("a1"), _
                     SearchOrder:=xlByRows, _
                     SearchDirection:=xlPrevious).Row

    Dim myrange1 As Range
    Set myrange1 = Sheets("base").Range("A1:H" & rn1)

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=myrange1, _
        Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
    Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("GL Amount"), "Sum of GL Amount", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of GL Amount")
        .NumberFormat = "0.00_);(0.00)"
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch #")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("GL Code")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch #").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveWorkbook.ShowPivotTableFieldList = False

[jawahars]

same error is poping up

type mismatch

Yellow highlited line below

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=myrange1, _
Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14

[Rudi]

It runs on my PC without a problem. (I am using Excel 2013 though!)

Try changing the Version:=xlPivotTableVersion14 to Version:=xlPivotTableVersion10
Also, if it does not work with PivotCaches.Create, try PivotCaches.Add again!

[HansV]

The problem is that you don't specify the TableDestination. Try this, for example:

Code: Select all

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=myrange1).CreatePivotTable _
        TableDestination:=Range("K3"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14

[Rudi]

If no table destination is supplied it seems to default to cell A1 of a new sheet.

Further experiments seem to work if the Version arguments are removed.
If the code above does not work, try this version without the Version arguments. (It also works without debugs in Excel 2013)

Code: Select all

    Sheets("base").Select
    rn1 = Cells.Find(What:="*", after:=Range("a1"), _
                     SearchOrder:=xlByRows, _
                     SearchDirection:=xlPrevious).Row

    Dim myrange1 As Range
    Set myrange1 = Sheets("base").Range("A1:H" & rn1)
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=myrange1) _
        .CreatePivotTable TableDestination:="", TableName:="PivotTable1"
    Cells(1, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("GL Amount"), "Sum of GL Amount", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of GL Amount")
        .NumberFormat = "0.00_);(0.00)"
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch #")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("GL Code")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch #").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveWorkbook.ShowPivotTableFieldList = False

[HansV]

If no table destination is supplied it seems to default to cell A1 of a new sheet.

I consistently got an error message if "" is specified as TableDestination...

[Jan Karel Pieterse]

Confirmed, if tabledestination is omitted, Excel 2010 errors out (even though recording a macro records it as "")

[Rudi]

If no table destination is supplied it seems to default to cell A1 of a new sheet.

I consistently got an error message if "" is specified as TableDestination...

That is strange...To confirm, you are using Excel 2013, right? Jan Karel confirms that 2010 debugs, but my 2013 runs the code fine.

[HansV]

I tested in Excel 2013.

[Rudi]

Does this workbook error for you? It runs fine for me... (just out of interest)?

1.jpg

[HansV]

I'll check later (I'm on another computer at the moment)

[Jan Karel Pieterse]

Seems to work fine on my Excel 2010.

[HansV]

Rudi, your macro works for me in Excel 2013 too - it does create a new sheet for the pivot table. Mysterious...

[Rudi]

Thanks for confirming guys.
Mysterious is the operative word!

However, it is all up to jawahars with plenty of feedback to review...