Help on VBA to select all column dates into pivot fields

Any of our VBA experts want to tackle this 1 please?

I will push this again for you

if im understanding what you are wanting....xlsum from first month until CRM
then this code could work

essentially loop every header from 9 until CRM adding datafield

Option Explicit

Sub pivt()
    
    Dim r As Long 'rows
    Dim c As Long 'columns
    Dim j As Long 'number of months
    Dim counter As Integer
    Dim colField As String
    
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    
    Set ws1 = ActiveWorkbook.Sheets("DIS_Q1-Q4")
    r = ws1.Range("A1").End(xlDown).Row
    c = ws1.Range("A1").End(xlToRight).Column
    
    On Error GoTo Err:
    j = ws1.Range("1:1").Find("CRM").Column - 1
    counter = 9
    
    Set ws = Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="DIS_Q1-Q4!R1C1:R" & r & "C" & c).CreatePivotTable _
            TableDestination:=ws.Name & "!R3C1", TableName:="PivotTable4"
            
    With ActiveSheet.PivotTables("PivotTable4")
        'no longer required
        '.AddDataField ActiveSheet.PivotTables("PivotTable4").PivotFields("Capitalized Commissions"), "Sum of Capitalized Commissions", xlSum
        '.PivotFields("CALL TYPE").Orientation = xlColumnField
        '.PivotFields("Capitalized Commissions").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
        
        .TableStyle2 = ""
        .RowAxisLayout xlCompactRow
        .PivotFields("Capitalized Quarter").Orientation = xlPageField
        
        .PivotFields("PUDO").Orientation = xlRowField
        .PivotFields("PROD TYPE").Orientation = xlRowField
        
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        
        For counter = 9 To j
            colField = ws1.Cells(1, counter).Text
            .AddDataField ActiveSheet.PivotTables("PivotTable4").PivotFields(colField), "Sum of " & colField, xlSum
        Next counter
        
        ActiveSheet.Name = "Amortization"
        ActiveWindow.Zoom = 85
        
    End With
       
    MsgBox ("Pivot Done")
    
    Exit Sub
    
Err:
    MsgBox "CRM header not found"

End Sub

Where did you get this code from in the first place ??

@snb
Dear sir

i found across google only and made changes according as per my requirement

Dear expert

I have changed range of pivotfield date column from 16 to j and also changed "FT" but it doesnt works for me.

Option Explicit

Sub pivt()
    
    Dim r As Long 'rows
    Dim c As Long 'columns
    Dim j As Long 'number of months
    Dim counter As Integer
    Dim colField As String
    
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    
    Set ws1 = ActiveWorkbook.Sheets("DIS_Q1-Q4")
    r = ws1.Range("A1").End(xlDown).Row
    c = ws1.Range("A1").End(xlToRight).Column
    
    On Error GoTo Err:
    j = ws1.Range("1:1").Find("FT").Column - 1 'replace of j = ws1.Range("1:1").Find("CRM").Column - 1'
    counter = 16 'replace of colunter = 10
    
    Set ws = Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="DIS_Q1-Q4!R1C1:R" & r & "C" & c).CreatePivotTable _
            TableDestination:=ws.Name & "!R3C1", TableName:="PivotTable4"
            
    With ActiveSheet.PivotTables("PivotTable4")
        'no longer required
        '.AddDataField ActiveSheet.PivotTables("PivotTable4").PivotFields("Capitalized Commissions"), "Sum of Capitalized Commissions", xlSum
        '.PivotFields("CALL TYPE").Orientation = xlColumnField
        '.PivotFields("Capitalized Commissions").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
        
        .TableStyle2 = ""
        .RowAxisLayout xlCompactRow
        .PivotFields("Capitalized Quarter").Orientation = xlPageField
        
        .PivotFields("PUDO").Orientation = xlRowField
        .PivotFields("PROD TYPE").Orientation = xlRowField
        
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        
        For counter = 16 To j 'replace of For counter = 9 To j
            colField = ws1.Cells(1, counter).Text
            .AddDataField ActiveSheet.PivotTables("PivotTable4").PivotFields(colField), "Sum of " & colField, xlSum
        Next counter
        
        ActiveSheet.Name = "Amortization"
        ActiveWindow.Zoom = 85
        
    End With
       
    MsgBox ("Pivot Done")
    
    Exit Sub
    
Err:
    MsgBox "FT header not found"

End Sub

please find the attachment some extent some of range of field in dates

Thanks for the assist, guys

Thanks alot Experts Voluntarily who helped across my post

which is driven me one more Last requirement!! I dont want add up all dates into datafield. I want add inputbox while run pivot intially it has to ask enter date from which entering date it has to take 10 dates towards right either it has less than 10 should not more than 10 but till end of CRM.

if you help me out with this condition more grateful to you

find the attachment

Change counter equal 17 and then subsequently start loop on 17 instead of 16
also change the colfield to extract the value in the same format as the header

        For counter = 17 To j
            colField = Format(ws1.Cells(1, counter), "dd-mmm-yy")
            .AddDataField ActiveSheet.PivotTables("PivotTable4").PivotFields(colField), "Sum of " & colField, xlSum
        Next counter

Now i replace as j = ws1.Range("1:1").Find("ST").Column from is j = ws1.Range("1:1").Find("FT").Column its not take date into datafield

as well one more requirement i want select Q1-13 only in report filter add this condition in code.

Option Explicit

Sub pivt()
    
    Dim r As Long 'rows
    Dim c As Long 'columns
    Dim j As Long 'number of months
    Dim counter As Integer
    Dim colField As String
    
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    
    Set ws1 = ActiveWorkbook.Sheets("Global Waterfall Schedule")
    r = ws1.Range("A1").End(xlDown).Row
    c = ws1.Range("A1").End(xlToRight).Column
    
    On Error GoTo Err:
    j = ws1.Range("1:1").Find("ST").Column - 1 'replace of j = ws1.Range("1:1").Find("CRM").Column - 1'
    counter = 16 'replace of colunter = 10
    
    Set ws = Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Global Waterfall Schedule!R1C1:R" & r & "C" & c).CreatePivotTable _
            TableDestination:=ws.Name & "!R3C1", TableName:="PivotTable4"
            
    With ActiveSheet.PivotTables("PivotTable4")
        'no longer required
        '.AddDataField ActiveSheet.PivotTables("PivotTable4").PivotFields("Capitalized Commissions"), "Sum of Capitalized Commissions", xlSum
        '.PivotFields("CALL TYPE").Orientation = xlColumnField
        '.PivotFields("Capitalized Commissions").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
        
        .TableStyle2 = ""
        .RowAxisLayout xlCompactRow
        .PivotFields("Capitalized Quarter").Orientation = xlPageField
        .PivotFields("Capitalized Quarter").PivotItems("Q1-13").Visible = True
 'i tried this

        .PivotFields("Region").Orientation = xlRowField
                .PivotFields("EVP-1").Orientation = xlRowField
                 .PivotFields("Cost Center").Orientation = xlRowField
        
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        
        For counter = 17 To j
            colField = Format(ws1.Cells(1, counter), "dd-mmm-yy")
            .AddDataField ActiveSheet.PivotTables("PivotTable4").PivotFields(colField), "Sum of " & colField, xlSum
        Next counter
        
        ActiveSheet.Name = "Amortization"
        ActiveWindow.Zoom = 85
        
    End With
       
    MsgBox ("Pivot Done")
    
    Exit Sub
    
Err:
    MsgBox "ST header not found"

End Sub

you didnt update

   counter = 16 'replace of colunter = 10

this needs to be 17

you seem to sort of know how to amend code but it seems you have trouble with syntax

here is some reading material for you

http://www.thespreadsheetguru.com/bl...l-pivot-tables
http://codevba.com/excel/PivotTable.htm
http://www.databison.com/how-to-read...ble-using-vba/