Summary
This is a short, but smart macro. It splits & saves the data on the active sheet into different CSV files. The newly created files are stored in a new folder called CSV output at the same location as your Excel file.
VBA macro
Sub GenerateCSV()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
iCol = 2 '### Define your criteria column
strOutputFolder = "CSV output" '### Define your path of output folder
Set ws = ThisWorkbook.ActiveSheet '### Don't edit below this line
Set rngLast = Columns(iCol).Find("*", Cells(1, iCol), , , xlByColumns, xlPrevious)
ws.Columns(iCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUnique = Range(Cells(2, iCol), rngLast).SpecialCells(xlCellTypeVisible)
If Dir(strOutputFolder, vbDirectory) = vbNullString Then MkDir strOutputFolder
For Each strItem In rngUnique
If strItem <> "" Then
ws.UsedRange.AutoFilter Field:=iCol, Criteria1:=strItem.Value
Workbooks.Add
ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=[A1]
strFilename = strOutputFolder & "\" & strItem
ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False
End If
Next
ws.ShowAllData
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Save it in a new VBA module
Understanding the code
iCol = 2
strOutputFolder = "CSV output"
The first line is your criteria column. A 1
would stand for column A, 2
for column B and so on.
Second, we define a folder name where all our CSV files should be saved at. You can also set a fully qualified path like C:\some\folder
. Otherwise Excel will create a folder at your Excel file's location
Set ws = ThisWorkbook.ActiveSheet
Here we save our current workbook and worksheet in a variable. Its not neccessarry to do this, but since we are dealing with multiple workbooks (newly created ones) I recommend this
Set rngLast = Columns(iCol).Find("*", Cells(1, iCol), , , xlByColumns, xlPrevious)
ws.Columns(iCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUnique = Range(Cells(2, iCol), rngLast).SpecialCells(xlCellTypeVisible)
Ok, what does this part? First, we search the last cell only in the criteria column. This must be done before our filtering and is needed later. Then, we use the famous advanced filter method to filter out in place all duplicate values from our criteria column. At last, we save all visible cells in a variable called rngUnique
If Dir(strOutputFolder, vbDirectory) = vbNullString Then MkDir strOutputFolder
Lets see if a folder called CSV output
already exists. If not, create one
For Each strItem In rngUnique
If strItem <> "" Then
[...]
End If
Next
Now, we start to loop through all unique values in our variable rngUnique. But empty values are skipped
ws.UsedRange.AutoFilter Field:=iCol, Criteria1:=strItem.Value
An important line. We use the autofilter method and view all lines which match our current unique value. The old advanced filter gets canceled automatically.
Workbooks.Add
ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=[A1]
These two lines create a new empty workbook and copy over only visible cells from our input Workbook
strFilename = strOutputFolder & "\" & strItem
Here we put together the CSV path. We take the current unique value as file name. The extension CSV is appended automatically since we have chosen xlCSV
as output format.
Make sure, your unique values do not contain invalid filename characters like < > | / * \ ? "
or the corresponding CSV file won't be created
ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False
The last step is to save the current workbook as a CSV and take the variable strFilename as filename. The CSV delimiter depens on your regional setting delimiter. It's possible to change the fileformat, eg. to tab delimited CSV or Excel 2003 workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
The first line speeds up our macro a bit since Excel doesn't need to show every single step of filtering.
The second line suppresses annoying File already exists prompts. Later we enable those functions again
This macro will color each row according to the value in column "F".
If the row is empty or the value is not a number, it will be skipped.
Sub color()
Dim color As Integer
For Each cell In Sheets(6).Range("F2:F65536")
If IsEmpty(cell) Then GoTo nextcell:
If Not IsNumeric(cell.Value) Then GoTo nextcell:
If cell.Value > 50 Then
color = 4
ElseIf cell.Value < 35 Then color = 3
Else: color = 2
End If
cell.EntireRow.Interior.ColorIndex = color
nextcell:
Next cell
End Sub
In your code above you are using two types of loops simultaneously. The For Each
loop you used loops through each element of the array you provide it. In this case the array is a range of cells.
Each iteration of a For Each
loop uses the variable name you provide in the declaration as a reference to the element of the array. So in the above instance, each iteration will assign a cell in column F to the variable name I gave cell
.
All that to say that when you are using a For Each
loop, you don't need to use an incrementing index variable (like i
) to call a cell because you already have all the cell object being pointed to with your variable cell
.
Best Answer
First, you need to create the chart, and whenever you're done. You only need to select the Status column and go to Home tab, and use Conditional Formatting to make the proper fill and font colors on cells based on a specific text.
To do that, you will need to go to the Conditional Formatting and then choose Manage Rules.
From the Manage Rules dialog, add a New Rule.
From the New Formatting Rule dialog, Do the following:
Specific Text
|containing
|"Your Status"
(put your status between quotes)Repeat the steps until you cover all status. Then, click OK and you're done.
Here is an example of what it will looks like :
As you can see, I created the chart first, then I added 5 rules on the column
A:A
for each word same steps that I explained earlier, and then match the cells colors with the chart colors. The total table is just to get the total number of each status, which I used to create the chart. (I usedSUMIF
to sum all numbers with matched status)