VBA Filter copy table with no data - copy whole table

Nerdalert314

New Member
Joined
Mar 28, 2018
Messages
23
Hello,

I'm not the best at this yet so please don't judge my code below... I am having a problem in the code where I highlighted it blue. I know there are some options like using an IF statement but I can never get those to work.

We have 13 tabs for the 13 weeks in the quarter. As you can see my fancy find and replace, replaces weeks of the year and adjusts it to one of the 13 weeks of the quarter. My problem with this Macro is when there is no data for the specific week it copies of the whole table. Can someone help me fix this?

If we are on week 2 of the quarter we will have data for 1 & 2
If we are on week 13 of the quarter we will have data for all 13 weeks

And so...

Please and thank you!




Code:
Sub Test()
'
' Test Macro
'
   
    Sheets("Cash Update Data").Select
    


    


    Columns("F:F").ColumnWidth = 18.13
    Columns("I:I").ColumnWidth = 8.38
    Columns("J:J").ColumnWidth = 7.88
    Columns("K:K").ColumnWidth = 6.5
    Columns("L:L").ColumnWidth = 7.88
    Columns("N:Q").Select
    Selection.Delete Shift:=xlToLeft


    
    Range("f:f").Select
    With Range("f:f")
    .Replace "*CF", ""
    .SpecialCells(xlBlanks).EntireRow.Delete
    End With




    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1").CurrentRegion, , xlYes).Name _
        = "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight20"


    ActiveWorkbook.Worksheets("Cash Update Data").ListObjects("Table1").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Cash Update Data").ListObjects("Table1").Sort. _
        SortFields.Add2 Key:=Range("Table1[[#All],[ACCOUNTING_DT]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Cash Update Data").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
  
    
    Workbooks.Open Filename:= _
        "NAME HERE.xlsx"
    
    
    


    Windows("Cash Projections Template - Premier Central Toni Q3'19.xlsx").Activate
    Sheets("QTD Collected").Select
    
    
    
    
    
    
    Range("$O2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Delete
    Windows("Daily Cash.xlsm").Activate
    Range("Table1").Select
    Selection.Copy
    Windows("Cash Projections Template - Premier Central Toni Q3'19.xlsx").Activate
    
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("N2").ActiveCell.FormulaR1C1 = "=VLOOKUP([@NAMESHORT],'Cash Projections'!C[-13],1,0)"
    Range("O2").ActiveCell.FormulaR1C1 = "=WEEKNUM([@[ACCOUNTING_DT]])"
    Range("QTD_Collected[[#Headers],[Vlookup]]").Select
    Selection.Cut
    Range("QTD_Collected[[#All],[Week '#]]").Select
    Range("QTD_Collected[[Vlookup]:[Week '#]]").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("O:O").Select
    Selection.Replace What:="14", Replacement:="1", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="15", Replacement:="2", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="16", Replacement:="3", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="17", Replacement:="4", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="18", Replacement:="5", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="19", Replacement:="6", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="20", Replacement:="7", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="21", Replacement:="8", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="22", Replacement:="9", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="23", Replacement:="10", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="24", Replacement:="11", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="25", Replacement:="12", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="26", Replacement:="13", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="27", Replacement:="1", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="28", Replacement:="2", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="29", Replacement:="3", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="30", Replacement:="4", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="31", Replacement:="5", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="32", Replacement:="6", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="33", Replacement:="7", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="34", Replacement:="8", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="35", Replacement:="9", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="36", Replacement:="10", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="37", Replacement:="11", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="38", Replacement:="12", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="39", Replacement:="13", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="40", Replacement:="1", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="41", Replacement:="2", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="42", Replacement:="3", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="43", Replacement:="4", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="44", Replacement:="5", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="45", Replacement:="6", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="46", Replacement:="7", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="47", Replacement:="8", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="48", Replacement:="9", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="49", Replacement:="10", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="50", Replacement:="11", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="51", Replacement:="12", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="52", Replacement:="13", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    
    
        
        
        
        
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=14, Criteria1:="<>#N/A"
    
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("1"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 1").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
[COLOR=#0000ff]    Sheets("QTD Collected").Select[/COLOR]
[COLOR=#0000ff]    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _[/COLOR]
[COLOR=#0000ff]    Array("2"), Operator:=xlFilterValues[/COLOR]
[COLOR=#0000ff]    [/COLOR]
[COLOR=#0000ff]    Range("QTD_Collected").Select[/COLOR]
[COLOR=#0000ff]    Selection.Copy[/COLOR]
[COLOR=#0000ff]    Sheets("Wk 2").Select[/COLOR]
[COLOR=#0000ff]    Range("$A$2").Select[/COLOR]
[COLOR=#0000ff]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=#0000ff]        :=False, Transpose:=False[/COLOR]
        
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("3"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 3").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("4"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 4").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("5"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 5").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("6"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 6").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("7"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 7").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("8"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 8").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("9"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 9").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("10"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 10").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("11"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 11").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("12"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 12").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("QTD Collected").Select
    ActiveSheet.ListObjects("QTD_Collected").Range.AutoFilter Field:=15, Criteria1:= _
    Array("13"), Operator:=xlFilterValues
    
    Range("QTD_Collected").Select
    Selection.Copy
    Sheets("Wk 13").Select
    Range("$A$2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    


    
    
    
    
    
    
    
    
    
    


End Sub
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,870
Members
410,711
Latest member
Josh324
Top