Pivot Table not refreshing properly

I have some data downloaded by means of a query created in VBA.
This data is then modified by adding columns of formulae.
A pivot table is then generated from the data and is further modified.
This is all done by procedures I have used many times before.
This time however when the pivot table is refreshed the two data columns are left out. This is unexpected and I cant see what I may have done differently to cause the problem.
I have prepared the attached workbook using the same code as the original in the hope that it would show what happens, but it works. (Click an option button and press "Choose a Month"
on UserForm2 to fire the code.)
I would guess that there will be code that would give more control over the pivot refresh than my Sub (DoPivot). Would that be a way forward?
John

CK76
Thanks for your help(Again!). Using PivotTables(1) makes no difference. The two value fields are still missing. If I rebuild the table and then do a manual refresh it works. If I manually refresh the partial table it refreshes partialy. I have made a new version on a new sheet and the same fault persists. I have changed PivotTable1 for PivotTable3 in DoPivot and was unable to find PivotTable3. (I had changed the name manually earlier).
Is it possible to list the names of the pivottables on a worksheet? I can see that it is better to use PivotTables(1) as long as I am looking for the first.
John

Sorry, I should have been clearer. The two fields that go missing are Sum Of Prior_Yr_Mth7 and Sum of Balance_Mth7. Those are the fields that are selected from the database according to the month chosen by the selected option button on UserForm2.
The value affects SQLStr.
John

I have noticed that when I refresh the pivot either manually or through the program I get a warning "There is already data in PivotTable do you want to replace it"?
I don't recall seeing this message before. Perhaps it provides a clue as to what is going on.
I have also noticed that when the fields go missing they have been unchecked in the field list.

Could my problem be caused by Pivottable option setting?

That makes sense. When the PivotTable changes it looses the two fields that I want and replaces them with spaces for all the fields of the table I am "pivoting". Those extra columns cover part of the range where I am making calculations.
I still have no idea why the simple refresh code isn't working, and I think I may have to redo the PivotTables each time. I hope your link may help there.
John

Bingo! You have got it. The databases I query have fields for every month of this year and every month of last year. My query asks for the results for month n for this year and last year.
If n changes from the time the query was set up the two fields will be missing.
Is there a way of keeping a constant field name for the pivottable even though the results come from different database fields?

John

CK76
I think the last posts crossed each other. in case my last did not arrive, thanks once again for your help. It solved a problem that had stopped me for days.
I told you that I had worked round the problem by copying the data from the query into a second table with static headings. I will now follow your much more elegant solution.
John