Hi guys,

I am almost done with my first set of code.

What I am trying to do is get a report formatted for email sending.

I have a list of data, on a single table, and I am trying to filter them onto different tables.

What I normally do is filter data>create header cell>copy filtered data>hide copied data and clear filters>repeat.

What got me stuck was, I realized that I won't always have data for a certain column, and I will have to remove that blank table if it gets created.

I learned about the code "If Range("A1").Value = 0 Then", and I was trying to incorporate it into my table, but I got completely lost in how the flow of the code should go. I understand that to make this particular code go smoothly, I need to create multiple subs in a single macro to make this work.

Once I figure out the appropriate way to use the if value code, I can then continue with the rest of the code.

Here is what I have so far:

 
Sub Ecom_Aux_Dumptest()

'


Dim aux As Worksheet
Dim dump As Worksheet


Set aux = Sheets("AUX")
Set dump = Sheets("Aux Macro Dump")


Application.ScreenUpdating = False
' Hide columns
    aux.Columns("F:F").EntireColumn.Hidden = True
    
' Filter according to color
    aux.Range("$A$1:$O$311").AutoFilter Field:=8, Criteria1:=RGB(255, _
        199, 206), Operator:=xlFilterCellColor
        
' Adding Break text, with formatting
    With dump.Range("A1")
        .Value = "BREAK"
        .Name = "Tahoma"
        .Font.Size = 10
        .Font.Bold = True
        .Interior.Color = RGB(255, 255, 0)
    End With


' Copying and pasting the data
    aux.Columns("A:H").Copy
    dump.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
' Clear Filters and Hide Cell
    aux.ShowAllData
    aux.Columns("H:H").EntireColumn.Hidden = True
    
' REPEAT Filter according to color
    aux.Range("$A$1:$O$311").AutoFilter Field:=9, Criteria1:=RGB(255, _
        199, 206), Operator:=xlFilterCellColor
        
        




Application.ScreenUpdating = True


End Sub



I attached a table for reference for what I am trying to achieve. It has been filtered to protect sensitive data.

Attempt.xlsx

Please help!