headsniper
11-10-2018, 05:52 AM
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.
23181
Please help!
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.
23181
Please help!