VBA Filter copy table with no data - copy whole table

N

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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

L
  • Question
Clean Text - Remove Repeat Line Breaks
Replies
4
Views
153
lneidorf
L
P
  • Question
Help with Vba Code
Replies
3
Views
146
Coyotex3
C
S
  • Question
Parse data - Overwrite lines with updated data
Replies
0
Views
110
Scifo
S
P
  • Solved
Understanding VBA Macro
Replies
5
Views
300
RoryA
RoryA
F
  • Solved
Looping code sequence
Replies
5
Views
137
FrenchCelt
F

Forum statistics

Threads
1,214,599
Messages
6,120,453
Members
448,967
Latest member
grijken

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top