selecting columns

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!

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.

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.

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.

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.

    Sheets.Add
    Dim lRC As Long, lCC As Long
    lRC = Worksheets("Raw").Range("A" & Worksheets("Raw").Rows.Count).End(xlUp).Row   'Calculate the row height of the source date
    lCC = Worksheets("Raw").Columns(Worksheets("Raw").Columns.Count).End(xlToLeft).Column   'Calculate the column width of the source date
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Raw!R1C1:R" & lRC & "C" & lCC, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=Range("A3"), TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion14
    Cells(3, 1).Select

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

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.

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

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