VB Excel:

[niall_heavey]

Hi all,

I'm currently trying to use an inherited excel macro.
I did not write it (and would not have the skills to write VB either)
But i've been getting a number of errors while running this code. I've got through a lot of them but now can't figure this one out.
The error that comes up on screen says:

Run-time error 1004

The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

And when I go to debug it, the error is in this section (pt.RefreshTable is highlighted)

Sub AllWorkbookPivots()


Dim pt As PivotTable
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

        For Each pt In ws.PivotTables
                    pt.RefreshTable
        Next pt

    Next ws


End Sub

Does anyone know what might be causing this error?
I'm sure its something very simple and straight forward but I do not have much experience in VB so any help is appreciated.

Thanks,

N

[nmaillet]

It shouldn't be anything wrong with the code. Check the cells where the pivot table is getting its data from. At least the first row shouldn't contain any empty cells. See this article.

[AndreRet]

It seems that the pivot table field name showing or reporting data has been changed. In lamens terms, if your macro used to get a name from your data field called "name" and displayed it in your pivot table field called "pivotname" and you changed "pivotname" to say "anothername", it will throw the error you received.

Try and change the pivot field name back to what it used to be.

Post the code where the data is retrieved and shown in the pivot, that will give us a better idea of where the error occurs...

[niall_heavey]

Thanks very much for the replies,

Think this is what you mean when you say where the data is retreived!
This here runs the sql string to get the data and as far as I know then puts it into one sheet where all the other pivots then come from............

sub mystr()

sqlstring = SQL code is here...................................


strCon = connection is defined here



Sheets("Temp").Select

With ActiveSheet.QueryTables.Add(Connection:=strCon, Destination:=Range("A1"), Sql:=sqlstring)
    .Refresh BackgroundQuery:=False

End With

Call FormulUpd.FscanUpDs
    Application.DisplayAlerts = False

End Sub

[nmaillet]

Ok, that may be the root of the problem, but you'll have to check the sheet where the data gets dumped to (after the query is run). What you want to see is data headers, in the first column, then all the data below that. The error message you are getting is usually caused by a header being an empty cell. Make sure that each header has something in it (I don't know if that query outputs headers for you, check that).

[AndreRet]

Can you post the code that fires under the "Call FormulUpd" macro event.