Project Excel: calcuate column values, use checkboxes in columns, and, have Excel take rows from one sheet that meet varying criteria and put those rows into other sheets

Purpose
The purpose of this project is to show to calcuate column values, use checkboxes in columns, and, have Excel 2010 take rows from one sheet that meet varying criteria and put those rows into other sheets. Each endeavor is broken down into it's own section below.

Enable the Developer Tab
Before going too far, if you are interested in writing some visual basic code, macros, and so forth then you will need to enable the developer tab as shown:

(Enlarge)

Enabling the developer tab is done via File -> Options -> Customize Ribbon -> Main Tab -> Developer (needs to be checked).

Calculating Columns
Checkboxes, as columns, may be a challenging object to work with unless Marlett checkboxes are used (more on that later). With Marlett checkboxes it is possible to calculate how many are checked, unchecked as well as calculating percentages.

(Enlarge)	In this example, the algorithm "=COUNTIF($C$2:C$9, "a")" is used to calculate how many rows have checked checkboxes. Here column C from row 2 to row 9 is what to calculate and if the rows have "a" in them, then they get calculated.
(Enlarge)	In this example, the algorithm "=COUNTBLANK($C$2:C$9)" is used to calculate how many rows have unchecked checkboxes. Here column C from row 2 to row 9 is what to calculate and if the rows have "" (blank) in them, then they get calculated.
(Enlarge)	In this example, the algorithm "=($C$12/8)" is used to calculate the percentage of unchecked checkboxes by taking the value of C12 and dividing that by 8 (number of rows with checkboxes). The column was formatted as a percentage in advance to remove the decimal point and place a percentage sign onto the end of the numeric value.

Excel 2010 & Visual Basic Scripting
With the developer tab enabled, and some visual basic scripting, you can have Excel 2010 automatically (1) allow Marlett checkboxes to be used as seen in the "Calculating Columns" above, (2) and have Excel 2010 copy rows of data from one sheet to another sheet depending on varying criteria you may wish to apply to a sheet. Let's see how that is done.

(Enlarge)

Accessing the scripting window is accomplished by Developer Tab -> Visual Basic.

(Enlarge)

In the Visual Basic window, you'll see some code already setup.

"Worksheet_BeforeDoubleClick" is responsible for activating Marlett checkboxes in C and D columns from row 2 to row 9:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
	'Integrate checkbox functionality into C and D columns
	If Not Intersect(Target, Range("C2:C9")) Is Nothing Then
		Cancel = True
		Target.Font.Name = "Marlett"
		If Target = vbNullString Then
			Target = "a"
		Else
			Target = vbNullString
		End If
	End If
	If Not Intersect(Target, Range("D2:D9")) Is Nothing Then
		Cancel = True
		Target.Font.Name = "Marlett"
		If Target = vbNullString Then
			Target = "a"
		Else
			Target = vbNullString
		End If
	End If
End Sub

"ApplyAutoFilters()" is responsible for copying rows from one sheet to other sheets based on criteria of (1) rows that are checked, (2) rows that are not checked:

Sub ApplyAutoFilters()
	' Copy row data to sheet 2 which have 'in computer' checked
	With Sheet1
		.Range("A1:D9").AutoFilter Field:=4, Criteria1:="<>"
		.Range("A1:D9").CurrentRegion.Copy Destination:=Sheet2.Range("A1").End(xlUp)
		.Range("A1:D9").AutoFilter
	End With
	' Copy row data to sheet 3 which do not have 'in computer' checked
	With Sheet1
		.Range("A1:D9").AutoFilter Field:=4, Criteria1:=""
		.Range("A1:D9").CurrentRegion.Copy Destination:=Sheet3.Range("A1").End(xlUp)
		.Range("A1:D9").AutoFilter
	End With
End Sub

How do you get the visual basic code to run? There a "play" button at the top of the window as shown that, once it is clicked on, will execute the functions you've defined.

Letting Excel Copy Rows From One Sheet To Another Sheet
Armed with the function "ApplyAutoFilters()" above in place, we can observe that Excel has moved applicable checked rows to sheet 2 and unchecked rows to sheet 3. Something like this can be very handy if you have hundreds or thousands of rows to analyze.

(Enlarge)	Here you can see we've got a myriad of rows with checked and unchecked columns (under column D).
(Enlarge)	After running the visual basic code "ApplyAutoFilters()" we can see that Excel 2010 has copied the rows with checked values to sheet 2.
(Enlarge)	After running the visual basic code "ApplyAutoFilters()" we can see that Excel 2010 has copied the rows with unchecked values to sheet 3.

You can Download the Excel 2010 Tutorial File so that you can try it out, and expand on it yourself.