[tpkelley]

I am trying to put an exported file from an accounting program into a excel table. I am having a problem with copying the posting month. The rows will be the variable. The posting moth will be column B. I need the posting month to auto copy next to the date. Also, I only need the data that has the new posting month because I am putting it in a pivot table. I may have one row or five hundred rows. Also, I may not have any cost for that month. Here is a macro that was developed. It is counting the dates, and that will not work because if a month does not have any cost, it will be off by one month from that point.

copy of macro:

Sub delcol()
'
' delcol Macro
' to del col it exported excel
'
' Keyboard Shortcut: Ctrl+d
'
Range("1:1,11:11,2:2,3:3,4:4,5:5,6:6,7:8,9:9,10:10").Select
Range("A10").Activate
Selection.Delete Shift:=xlUp
Range("A11").Select
Selection.Cut Destination:=Range("B1:E1")
Range("A:A,F:F,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,S:S").Select
Range("S1").Activate
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Dim cl As Range
For Each cl In Columns(2).SpecialCells(2)
If IsDate(cl) Then
Cells(Rows.Count, 14).End(xlUp).Offset(1).Resize(, 5) = cl.Resize(, 5).Value
Cells(Rows.Count, 13).End(xlUp).Offset(1) = WorksheetFunction.CountIf(Range(Range("B1"), cl.Address), "/")
End If
Next cl
Columns("M:R").Select
Selection.Cut Destination:=Columns("H:M")
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1:E1").Select
Selection.Cut Destination:=Range("I1:M1")
Range("H3").Select
ActiveCell.FormulaR1C1 = "Posting"
Range("I3").Select
ActiveCell.FormulaR1C1 = "Date"
Range("J3").Select
ActiveCell.FormulaR1C1 = "Amount"
Range("K3").Select
ActiveCell.FormulaR1C1 = "Vendor Number"
Range("L3").Select
ActiveCell.FormulaR1C1 = "Vendor Name"
Range("M3").Select
ActiveCell.FormulaR1C1 = "Invoice Number"
Columns("A:G").Select
Selection.ClearContents
Range("H3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R3C8:R234C13").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Posting")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor Name")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice Number")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Amount")
.Orientation = xlRowField
.Position = 5
End With
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable1").Format xlReport5
Range("E3").Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
Range("F3").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Amount")
.NumberFormat = "#,##0.00"
End With
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

problem part:

Dim cl As Range
For Each cl In Columns(2).SpecialCells(2)
If IsDate(cl) Then
Cells(Rows.Count, 14).End(xlUp).Offset(1).Resize(, 5) = cl.Resize(, 5).Value
Cells(Rows.Count, 13).End(xlUp).Offset(1) = WorksheetFunction.CountIf(Range(Range("B1"), cl.Address), "/")
End If
Next cl