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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
104
vancher19
V
L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
317
Linda Lo
L
N
  • Question Question
Not understanding loop in vba
Replies
7
Views
299
jolivanes
jolivanes
J
  • Solved
Assistance with excel macro
Replies
3
Views
249
dreid1011
dreid1011
D
  • Solved
Range selection specific column with End(xlDown)
Replies
8
Views
307
dwalc
D

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

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