Hi, So i Have this VBA code that filters a table with the contents of a pivot table being it's criteria and pastes it to another location. and by the side of the filtered data it adds a timestamp column and adds date to every row of the filtered data
The problem that i am having is that i made a new file that needs the same thing to be done. and made the changes accordingly in the VBA code. But somehow the date is not being pasted in the new file.
Sub AdvanceFilter()
Dim Table As ListObject
Dim PT As PivotTable
Dim PasteCell As Range, n As Long 'variable, target cell for top left of pasted data
'Apply speed up options
Application.EnableEvents = False ' prevent event macros triggering
Application.ScreenUpdating = False ' to speed up macro
Application.Calculation = xlCalculationManual ' stop calcs
With Sheet14 ' specify the sheet
Set PT = .PivotTables("AdvanceFilterCriteriaPivot")
Set Table = .ListObjects("Master_IN_OUT")
Set PasteCell = .Range("CT" & .Range("CT" & .Rows.Count).End(xlUp).Row + 3) 'get CT and last used row + 3
' change target for paste to the above and copy
Table.Range.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=PT.TableRange1, Unique:=False
Table.Range.SpecialCells(xlCellTypeVisible).Copy Destination:=PasteCell
.ShowAllData
PasteCell.Offset(-1, 0).Value = Now 'add date and time above pasted data
PasteCell.Offset(-1, 1).Value = "Extract:" 'add name after the above
PasteCell.Resize(1, 17).Font.Color = vbBlack 'change pasted header row from white to black
'Put date (for XLOOKUP)
PasteCell.Offset(0, -1).Value = "Date paid" ' Add column heading
For n = PasteCell.Row + 1 To .Range("CT" & .Rows.Count).End(xlUp).Row
.Range("CS" & n).Value = Date ' insert today's date
Next n
'Turn calcs back on to evaluate XLOOKUP formulae
.Calculate
End With
Application.EnableEvents = True 'reset events, screen and calcs
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub