SamuelSeawall
New Member
- Joined
- Oct 21, 2021
- Messages
- 2
- Office Version
- 2019
- Platform
- Windows
VBA Code:
Sub CopFilteredData2()
Dim rng As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Transhipment - October 2021")
Application.ScreenUpdating = Not Toggle
Application.EnableEvents = Not Toggle
Application.DisplayAlerts = Not Toggle
Application.EnableAnimations = Not Toggle
Application.DisplayStatusBar = Not Toggle
Application.PrintCommunication = Not Toggle
Application.Calculation = IIf(Toggle, xlCalculationManual, xlCalculationAutomatic)
If ws.AutoFilterMode = False Then
ws.ListObjects("Inventory").Range.AutoFilter Field:=11, Criteria1:="On Terminal"
End If
Set rng = ws.ListObjects("Inventory").Range
rng.SpecialCells(xlCellTypeVisible).Copy 'Only the visible cells and not all of them
ws.ListObjects("Inventory").AutoFilter.ShowAllData 'This is intended to reset the filters on the 'Inventory' table
Dim wb As Workbook, NewWS As Worksheet
Set wb = Application.Workbooks.Add
Set NewWS = wb.Sheets(1)
ActiveWorkbook.Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range("$A$1:$N$1"), , xlYes).Name = _
"Table2"
NewWS.Paste
Sheets.Add After:=NewWS
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$A"), , xlYes).Name = _
"Table1"
Columns("A:A").Select
ActiveWorkbook.Names.Add Name:="Loaded", RefersToR1C1:= _
"=Table1[[#All],[Column1]]"
Sheets.Add After:=ActiveSheet
NewWS.Range("K2").FormulaR1C1 = "=IF([@[Match ]]>0,""LOADED"", ""ON TERMINAL"")"
NewWS.Range("L2").FormulaR1C1 = "=IFERROR(MATCH([@VIN],Loaded,0),0)"
NewWS.Range("N2").FormulaR1C1 = "=IFERROR(DAYS([@[Dep. date]],[@ETA]),0)"
Dim rng1 As Range
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Domestic - October 2021")
If ws1.AutoFilterMode = False Then
ws1.ListObjects("Table2").Range.AutoFilter Field:=9, Criteria1:="On Terminal"
End If
Dim Visible As Range
Set rng1 = ws1.ListObjects("Table2").Range
rng1.SpecialCells(xlCellTypeVisible).Copy 'Only the visible cells and not all of them
ws1.ListObjects("Table2").AutoFilter.ShowAllData 'This is intended to reset the filters on the 'Inventory' table