update pivot after changing fields

Dear experts,
I have an excel spreadsheet (attached) where a pivot is looking at a range of cells.
After the range is changing (columns titles are updated) I refresh the pivot but then it deletes all entrys and I have to rebuild the table.
I changed the formula's on page Site raw to values.
What happens id that the number of columns stays the same but the WK numbers are updated and they should be used as row labels in the pivot.

What is wrong?

Kind regards

Eric
Realisation-Week-Schedule2.xls
LVL 6
Eric ZwiekhorstSAP Business ConsultantAsked:
Who is Participating?
 
Rory ArchibaldCommented:
I tested that macro against the workflow you suggested and it worked fine every time. (note: it does not attempt to change the names, only the captions).
0
 
Rory ArchibaldCommented:
If you change column titles, then the field names the pivot uses no longer exist and it gets a little confused! Can you not change the headers before you create the pivot? (note: I haven't seen the file)
0
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
Hy Rorya,

I suspected something like this,

But I do not want the graph to show SUM if WK5-8, how can I preserve row names without it showing SUM of..
The pivot stays in the workvbook,  other sheets in this workbook are update with new week data and the array where the Pivot is looking at is updated. Each week that passes the WK5-8 would change to WK6-9 and so forth.. And I want the Pivot to follow this array..
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Rory ArchibaldCommented:
If you just want to change the captions, you can do that in the pivot table. Just right-click each field, choose Field Settings and enter whatever caption you want (as long as it does not match an actual field name).
0
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
Hi Rory,

I changed the Pivot so the chanaging colunm names are no longer in the pivot, I gave new not changing column names to use.
I tryed to alter the caption of the pivot field like you described but thru VBA.
The first time it works but when changing the cells that are used to alter the captions there is a problem.
Because what was in column M is now in column N , D is in C etc and the only new name is in M.
Now the macro will only try to change the caption of the last item (holding the old name that was in M.
But the name does no longer change with it.
all other captions give a prolbem because there still exists a name with this same text.
I tried to overwrite all captions and names with a counter value but the names don't seem to update?

Can anyone advice in this?


Kind regards

Eric
0
 
Rory ArchibaldCommented:
I didn't follow any of that, I'm afraid. Care to elaborate, preferably with a sample workbook?
0
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
Yes, I will upload the one I use for testing.

I will try to explane: When you are in a PIVOT table you see in the Pivot Table Field List the captions if the fields, in the column where I click on field settings I can alter the name. Like it seems they are not connected, I have for example 62 as copation and WK 9-12 as name.
Realisation-Week-Schedule2.xls
0
 
Rory ArchibaldCommented:
If I understand what you are trying to do, then all you need is:
Sub ChangeCaption()
    Dim pt As PivotTable
    Dim newCaption As Variant
    Set pt = ActiveSheet.PivotTables(1)
    For i = 13 To 1 Step -1
      newCaption = Worksheets("Site raw").Cells(2, i + 2)
      pt.DataFields(i).Caption = newCaption
    Next
End Sub

Open in new window

0
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
Hi Rory,

Did you try it,

It does not work as the weeks will change normally we will update after 4 weeks so the name of the last file d wil be copied into the field before the last., but then I have a error in the macro..

I have a round work that works without the pivot table but I want to now how it have to do it.

Kind regards.
Eric
0
 
Rory ArchibaldCommented:
It worked for what I thought you wanted. Apparently I misunderstood (again) so can you explain the work flow in words of one syllable for me? :)
0
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
Well Rory,

It should change the row titles on the pivot.
They are marked WKx-x

If it worked for you try the following.
Go to site sheet and copy paste the headers one column to the left, then change the last column to 4 weeks higher

then update pivot again..


Kind regards


Eric
0
 
Rory ArchibaldCommented:
Do you mean the site sheet or the site raw sheet?
0
 
Rory ArchibaldCommented:
Assuming you meant "site raw", use this:
Sub ChangeCaption()
   Dim pt As PivotTable
   Dim newCaption As Variant
   Set pt = ActiveSheet.PivotTables(1)
   For i = 1 To 13
      pt.DataFields(i).Caption = "sometext" & i
   Next i
   For i = 1 To 13
      newCaption = Worksheets("Site raw").Cells(2, i + 2)
      pt.DataFields(i).Caption = newCaption
   Next
End Sub

Open in new window

0
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
Hi Rory,

the pivot is in Site, the data is in site raw

something like the marco you show I had in the workbook,
there is a problem with duplicate names or captions... for look at duplicates excel mixes them both but you can not assign name with macro?


Kind regards


Eric
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.