pivot update doesnt use new data

[AnnetteTR]

Hi I got a little stuck here

I have an excel file with data filled in a sheet with a Userform - hurra that works.
After having filled the sheet with data - it is time for calculating on the data and time to make overview over part of the data.

To that I have made a makro:

Sub AirVolumeOverview()
'
'
'
'    
Dim pt As PivotTable
Dim strField As String
Dim Sortering As String
Dim Forstevalg As String, Andetvalg As String, Tredjevalg As String, Fjerdevalg As String
Dim EnhedForstevalg As String, EnhedAndetvalg As String, EnhedTredjevalg As String, EnhedFjerdevalg As String
Dim Enhed1 As String, Enhed2 As String, Enhed3 As String, Enhed4 As String
 
'changes to the actual sheet
Sheets("Ventilation i rum").Select
 
'move to the topleft cell
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
 
' Naming the dataarea Items
Range(Selection.End(xlToRight), Selection.End(xlDown)).Name = "Items"
 
   Sortering = "Etage"
   Forstevalg = "Rum nr."
   Andetvalg = "Basis luftmængde [m³/h]"
   Tredjevalg = "Variabel luftmængde min"
   Fjerdevalg = "Variabel Luftmængde max"
 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
  SourceData:="Items").CreatePivotTable TableDestination:="", TableName:="LOlist"
 
Set pt = ActiveSheet.PivotTables("LOlist")
'Placering af Pivottabel
ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)
pt.AddFields RowFields:=Sortering    
 
With pt.PivotFields(Sortering)
    .Orientation = xlRowField
    .Position = 1
End With
 
   pt.AddDataField pt.PivotFields(Andetvalg), " " & Andetvalg, xlSum
   pt.AddDataField pt.PivotFields(Tredjevalg), " " & Tredjevalg, xlSum
   pt.AddDataField pt.PivotFields(Fjerdevalg), " " & Fjerdevalg, xlSum
 
With pt.PivotFields(Forstevalg)
    .Orientation = xlRowField
    .Position = 2
End With
 
'Formatting the valuearea
With pt.PivotFields(" " & Andetvalg)
        .NumberFormat = "0 ""m³/h"""
End With
 
With pt.PivotFields(" " & Tredjevalg)
        .NumberFormat = "0 ""m³/h"""
End With
With pt.PivotFields(" " & Fjerdevalg)
        .NumberFormat = "0 ""m³/h"""
End With
 
'Check if sheetname is used, puts the new sheet last in the row of sheets
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
    If sht.Name Like "AirVolumeOverview*" Then arknr = arknr + 1
Next sht
ActiveSheet.Name = "AirVolumeOverview" & arknr + 1
ActiveSheet.Move after:=Worksheets(Worksheets.Count)
End Sub

Seamingly this works fine - but now I have added some data.
And it is not used in the pivottable.
As you can see from the code - the range to use for the pivot is changed accordingly.

I have tried to refresh manually. It doesnt make any difference. Only if I reduce the area with one line - the pivot is updated.

Is there a way to clear the Cashe an start over when the macro is run?

Hope for help
Regards Annette

 

[Drumaster]

Hi Drummaster
I think you might have forgotten the equal sign
like:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:[SIZE=4][COLOR=red][B]=[/B][/COLOR][/SIZE]Range(Selection.End(xlToRight), Selection.End(xlDown))) _.CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable12", DefaultVersion:=xlPivotTableVersion10

Hope that helps.

When the text is on same line you dont need Space Underscore " _" this is used to tell vba that you are changing line but is in the same command.
(I dont know if it is a problem that it is there!)

Regards
Annette
I am using excel 2007

i wish that was it :s but nope getting crazy 2.5 hours i'm fighting this problem
but i never focus on the error message: PivotTable field name is not valid ???????.

 

[AnnetteTR]

OK - Take care there could be an invisible " "

Regards Annette