selecting columns

[nawGo]

Hi, quick question.

I have columns A-Z that I am using to create a pivot table,

however I want to include columns after Z as well that is (AA- AD) these columns are blank.

Is there a way to include them, I tried to include them but i get the following error:

error.PNG

Let me know, thanks!

[RudiS]

For a column to be valid and appear in a pivot, they must have column headings.
Add some dummy heading into those four blank columns and ensure that you reset the pivot range to include these in the pivot table. The should reflect then without creating the error.

[newdoverman]

Add the columns later as required. Click in the Pivot table then on the Options tab, click on Change Data Source, and re-select the data area.

[nawGo]

Thanks for your replies guys, I really appreciate them.

As far as the data source is considered it’s a great idea if I am doing it manually,
Since I am recording a macro and rerunning it wouldn’t work.

Is there a way I could increase my tables’ range in my macro?


Using the following macro:

Please Login or Register  to view this content.

[newdoverman]

Are you making use of the Relative/Absolute reference button when recording the macro? Use the absolute reference when going to the upper left of the data source then click Use Relative References and then Ctrl + Shift + right arrow then down arrow. This should select your table and be repeatable when the table expands.

[RudiS]

Hi nawGo,

An alternative to newdoverman's method is to use variables in the code to calculate the dimensions of your table.
The code below simulates the code you posted, but includes the variables to calculate the source table size and factor this into the creating of a new pivot.

Please Login or Register  to view this content.

[nawGo]

Thank you for your help newdoverman and rudis, I'll try your suggestions and let you know if it works!

[nawGo]

Okay guys i need help!!

So basically what i ended up doing was adding 5 columns with column headings (1-5) and auto filled those columns with =""
and recorded the pivot table macro. This worked like a charm!

However, the problem is that now when I change the column headings (1-5) to something else and refresh the pivot table, these changes aren't reflected and the column from the pivot table gets deleted.

My question now is can I change the pivot table macro in such a way that it's flexible with the column headings?

[RudiS]

Hi,

Controlling the dimension of the source data for the pivot table is easy based on your recorded macro discovery or the code I uploaded above, or even by name range or the new tables feature. When the source data is changed to include or exclude columns, and the pivot is refreshed, those columns would be automatically updated in the pivot table list. What is more complex to do in VBA is to program a variable pivot structure, since the field names are used in the code to determine the position they will occupy in the pivot table. Your description above is indicative of having flexible field heading being included in the table which should happen after you refresh the table.

I am not sure if your question focuses on the inclusion of the fields into the pivot field list, or are you trying to have those new fields positioned into the table itself? Please clarify. TX

[nawGo]

Hi RudiS,

sorry for not being clear. I'll attach a sample excel sheet and this way it would be easy to follow along. Thanks!

[nawGo]

Sample data and explanation attached.

[newdoverman]

The hard coding of the column names seems to be the problem. I was able to change column headers for columns B and C but not the others.

A VBA expert will likely know how to get around this.

[nawGo]

Thanks for trying. I think I will post this question to VBA/excel programming forum.

[nawGo]

Hey newdoverman, how were you able to change column headings for column B and C?

[newdoverman]

On the original worksheet, I just wrote in new titles then ran the macro. Those titles were picked up by the macro and installed in the Pivot Table. Columns A, D, and E didn't allow it.

Didn't do anything special.....hmmmm

[nawGo]

umm okayy. That gave me an idea...
I have changed my question and posted it here:

http://www.excelforum.com/excel-char...ease-help.html

[RudiS]

Reply here also:
http://www.excelforum.com/excel-char...ml#post3647746