• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset value PT + clear (blanks)

S

Soph'

New Member
Evening
2 issues here (VBA code below)

I have a dynamic pivot table: 2 columns are added at the end ("Add New Date" and "Add Net Change Date") every time it's worked on.

A. offset issue

I can't work out the offset formula that will make it work. The data for one of DataFields needs to be: lastColumn.Offset(0, -2)
The VBA code I have works and doesn't bug but it doesn't pull this "famous" column. The Last Column is always ("Add Net Change Date")
B. Blank not removed
I have included for the blank cells to be removed, but they still appear in the PT. Any idea of why?
Could you someone help ? Thanks a lot for the help.

Code: 
Sub PivotTableModel()

' PivotTable Macro
Dim objtable As PivotTable, objfield As PivotField
Dim NewCol&, LastRow&
Dim ws1 As Worksheet
Dim i As Long
Set ws1 = Worksheets("ACT HOURS VS BUDGET")
On Error Resume Next

'select sheet and 1st cell on the data table
ws1.Select
Range("A1").Select
'Create PivotTable based on the hour Datasheet
Set objtable = ws1.PivotTableWizard(Tabledestination:=Sheets.Add.Range("A1"))
'Specify row and Column fields.
With objtable
.PivotFields("MODEL").Orientation = xlRowField
.PivotFields("WC").Orientation = xlColumnField
lastColumn = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Selection.EntireColumn.Select
.PivotFields (.Range(lastColumn.Offset(0, -2))).Orientation = xlDataField
.PivotFields("Add New Date").Orientation = xlDataField
  With .DataPivotField
  .Orientation = xlColumnField
  .Position = 2
End With

'loop through all fields in PivotTable,set subtotals to false
With objtable
For Each objfield In .PivotFields
objfield.Subtotals(1) = True
objfield.Subtotals(1) = False
Next objfield
objtable.ManualUpdate = False
End With

'Repeat Labels in 1st Row
objtable.RepeatAllLabels xlRepeatLabels
End With

'Remove Blank cells
With objtable
If .Rows.Count = 1 Then Exit Sub
Application.ScreenUpdating = False
For i = .Rows.Count To 2 Step -1
If .Cells(i, 1) = "" Or .Cells(i, 1) = "(blank)" Then _
.Cells(i, 1).EntireRow.Hidden = True
Next i
End With
Application.ScreenUpdating = True
End
End Sub
 
Last edited by a moderator:
You'll get faster help if you attach sample file demonstrating your issue. Along with manually created desired output.
 
Couple of questions.

1. Do you need the pivottable recreated every time? Or simply updated with increased range?

2. You included row 519 in your pivottable range. This looks to be total row. Do you need it in Pivottable? Or can I exclude it?
 
Chihiro,
Q1: it needs to be updated each time: I need to report on the new date and the previous one because next report's previous date is today's new date. This is why I want to write an offset of -2 columns from "New Date". Once the pivot is done, the date is changed to the actual date and the PT is refreshed.

Q2: these VBA codes are to work for 4 other reports which I've made uniform to save time. None has the same length. I ran into an issue as I really don't know how to use VBA (I didn't know what it was 2 months ago) with summing up the entire column and adding an autosum on the last cell of said column. This is why I have row 519 (I am then sure all the data is included whatever the report). Feel free to re-write.
I hope this helps?
 
Try this.

Code: 
Sub CreatePivotTable()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRow As Long
Dim pvtRng As Range

Set ws1 = ThisWorkbook.Worksheets("ACT HOURS VS BUDGET")
lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

'Set range to be used as data source (CurrentRegion minus 1 row at end)
Set pvtRng = ws1.Cells(1).CurrentRegion.Resize(lRow - 1)

'Add new worksheet at end of tab list
Set ws2 = Worksheets.Add(After:=Worksheets(Worksheets.Count))

'Create PivotCache and generate Pivot Table in new worksheet, change Version & DefaultVersion as needed
'1 = Excel2002, 2 = Excel2003, 3=Excel2007, 4=Excel2010, 5=Excel2013, 6=Excel2016
    ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        pvtRng, Version:=6).CreatePivotTable _
        TableDestination:=ws2.Cells(1, 1), TableName:="PivotTable1", DefaultVersion _
        :=6
       
'Set values field using pvtRng. Row 1, Column = Column Count - 3 and - 1
    With ws2.PivotTables("PivotTable1")
        .AddDataField .PivotFields(pvtRng.Cells(1, pvtRng.Columns.Count - 3).Value), _
            "Sum of " & pvtRng.Cells(1, pvtRng.Columns.Count - 3).Value, xlSum
        .AddDataField .PivotFields(pvtRng.Cells(1, pvtRng.Columns.Count - 1).Value), _
            "Sum of " & pvtRng.Cells(1, pvtRng.Columns.Count - 1), xlSum
    End With

'Set RowField, assumed fixed location for header. C1
    With ws2.PivotTables("PivotTable1").PivotFields(pvtRng.Cells(1, 3).Value)
        .Orientation = xlRowField
        .Position = 1
    End With
   
'Set ColumnField, assumed fixed location for header. I1
    With ws2.PivotTables("PivotTable1").PivotFields(pvtRng.Cells(1, 9).Value)
        .Orientation = xlColumnField
        .Position = 1
    End With

'Changes PivotTable style
    ws2.PivotTables("PivotTable1").TableStyle2 = ""

End Sub
 
Woops, just noticed that I forgot to remove -1 from below section.

Code: 
'Set range to be used as data source (CurrentRegion minus 1 row at end)
Set pvtRng = ws1.Cells(1).CurrentRegion.Resize(lRow - 1)

Just use .Resize(lRow). I was originally using another column for lRow calculation, but since it's based on Column A, it's no longer needed.
 
Change as you advised however I keep getting a message error on this line of command:

.AddDataField .PivotFields(pvtRng.Cells(1, pvtRng.Columns.Count - 3).Value), _
"Sum of " & pvtRng.Cells(1, pvtRng.Columns.Count - 3).Value, xlSum

Run-Time error 1004: Unable to get the PivotField Property of the PivotTable class.

I've tried to run your macro without this command and it works fine. adding it back and it bugs. Doesn't make sense as it's same command as for the other dataField, difference in chosen column....
To be complete: it only bugs when I run it on the 2nd time. I add a date on new created column of 1st run, then ran macro "general" again and then yours; that's when it bugs.
 
Last edited:
here you go: 1st run is on sheet 2, 2nd run is on sheet 4.
Sheet 3 was from earlier on this afternoon. I've left macro "general" and added yours so you can have a go. thanks for your time,I appreciate.
 

Attachments

Ah I see what the issue is.

Cells with date values can't be used as .Value. Depending on your system's Date format setting it will contain illegal character for Field names (such as "/").

You can change .Value to .Text and it will work (since the cell contains 14-Mar).

Alternately you can change value in the cell from numeric date value to text string without the illegal characters.
 
You must log in or register to reply here.
Back
Top