cmed
asked on
create a summary excel tab
i want to create a summary tab after the user makes their selection in the rich text box. How do I name the summary tab in excel. The tab are named based on the date and term selected. I have the code of what I want the summary to do. I really just need to figure out how to name the summary because the user may choose some many or one date and term. Any suggestions
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"
Dim increment As Integer = 0
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DTPTerm1.Format = DateTimePickerFormat.Custom
DTPTerm1.CustomFormat = " "
'Setting up Progress Bar
If increment > ProgressBar1.Maximum Then
increment = ProgressBar1.Maximum
End If
ProgressBar1.Value = increment
End Sub
Private Sub DTPTerm1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTPTerm1.ValueChanged
DTPTerm1.CustomFormat = "MM/dd/yyyy"
End Sub
Private Sub btnAddTermStart_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAddTermStart.Click
RichTextBox1.SelectionStart = RichTextBox1.TextLength
RichTextBox1.SelectedText = DTPTerm1.Value.ToString("MM/dd/yyyy") & " " & COBTermType.SelectedItem & vbCrLf
End Sub
Private Sub btnClearTermStarts_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClearTermStarts.Click
RichTextBox1.Clear()
ProgressBar1.Value = increment
End Sub
Private Sub RichTextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RichTextBox1.TextChanged
End Sub
Private Sub CreateWorksheet(ByRef xlSheet As Excel.Worksheet, ByVal termStartDate As Date, ByVal termTypeFull As String, ByVal tableStyle As String)
xlSheet.Name = termStartDate.ToShortDateString.Replace("/", "-") & " " & termTypeFull
xlSheet.Activate()
Using cnn As New SqlConnection(connectionString)
cnn.Open()
'Run SQL'
Using mysda As New SqlDataAdapter()
Using mycmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)
mycmd.CommandTimeout = 0
'mycmd.Parameters.AddWithValue("@PROGRAM_START", termStartDate)
'mycmd.Parameters.AddWithValue("@TERM_TYPE", termTypeFull.Substring(0, 1))
mycmd.Parameters.AddWithValue("@PROGRAM_START", termStartDate)
mycmd.Parameters.AddWithValue("@TERM_TYPE", termTypeFull)
mysda.SelectCommand = mycmd
Using ds As New DataSet
mysda.Fill(ds)
'Display Headers, Columns, and Rows'
For i = 0 To ds.Tables(0).Columns.Count - 1
xlSheet.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
Next
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlSheet.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next
'Format Table'
xlSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet.ListObjects("Table1").TableStyle = tableStyle
'~~> Create Borders
With xlSheet.UsedRange
.Range("O:O").Cells.NumberFormat = "$ ###,###.00"
With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With
xlSheet.Columns.AutoFit()
End Using
End Using
End Using
End Using
End Sub
Private Function ValidateCheck() As Boolean
If DTPTerm1.CustomFormat = " " Then
MessageBox.Show("Please select a date.")
Return False
ElseIf COBTermType.SelectedIndex = -1 Then
MessageBox.Show("Please select a term type.")
COBTermType.Focus()
Return False
End If
Return True
End Function
Private Sub btnCreateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReport.Click
If ValidateCheck() = False Then
Exit Sub
End If
ProgressBar1.Value = increment + 5
Dim xlApp As New Excel.Application
Dim xlWorkBook = xlApp.Workbooks
Dim xlBook = xlWorkBook.Add
Dim xlSheets = xlBook.Worksheets
Dim misValue As Object = System.Reflection.Missing.Value
Do While xlSheets.Count > 1
Dim delSht = CType(xlSheets.Item(xlSheets.Count - 1), Excel.Worksheet)
delSht.Delete()
Loop
Dim shtCount = 1
For Each line In RichTextBox1.Lines
If line.Length > 0 Then
Dim whiteSpace = line.IndexOf(" "c)
Dim termDate = line.Substring(0, whiteSpace).Replace("/", "-")
Dim termType = line.Substring(whiteSpace + 1, 1)
If shtCount > 1 Then
xlSheets.Add(, xlSheets.Item(shtCount - 1))
End If
CreateWorksheet(xlSheets.Item(shtCount), termDate, termType, "TableStyleMedium1")
ProgressBar1.Value = increment + 15
shtCount += 1
End If
NextASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When i hard coded the dates the summary would format the following (see code). termstart1 and so forth were the dates that was preset. I want to replace those dates with the dates the user choose and repeat the each time the user choose a date. Any suggestion. I apologize in advance. I am new to programming.
With xlSheet7
.Range("B3").Value = termStart1
.Range("F3").Value = termStart2
.Range("J3").Value = termStart3
.Range("B22").Value = termStart4
.Range("F22").Value = termStart5
.Range("J22").Value = termStart6
.Range("B4, F4, J4, B23, F23, J23").Value = "Status"
.Range("B5, F5, J5, B24, F24, J24").Value = "Incomplete"
.Range("B6, F6, J6, B25, F25, J25").Value = "Ready To Package - Special Processing"
.Range("B7, F7, J7, B26, F26, J26").Value = "Ready To Package"
.Range("B8, F8, J8, B27, F27, J27").Value = "Awarded"
.Range("B9, F9, J9, B28, F28, J28").Value = "Declined Aid"
.Range("B10, F10, J10, B29, F29, J29").Value = "Not Eligible"
.Range("B11, F11, J11, B30, F30, J30").Value = "Inactive Awarded"
.Range("B12, F12, J12, B31, F31, J31").Value = "Inactive Not Awarded"
.Range("B13, F13, J13, B32, F32, J32, B20, F20, J20, B39, F39, J39").Value = "Total"
.Range("B15, F15, J15, B34, F34, J34").Value = "Active Students Days RP"
.Range("B16, F16, J16, B35, F35, J35").Value = "0 - 7"
.Range("B17, F17, J17, B36, F36, J36").Value = "'8 - 14"
.Range("B18, F18, J18, B37, F37, J37").Value = "15 - 21"
.Range("B19, F19, J19, B38, F38, J38").Value = "22+"
.Range("C4, G4, K4, C23, G23, K23, C15, G15, K15, C34, G34, K34").Value = "Students"
.Range("D4, H4, L4, D23, H23, L23, D15, H15, L15, D34, H34, L34").Value = "Percent"
.Range("C5").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""IP"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart1 & "'!$D:$D,""ID"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS""))"
.Range("C6").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DM"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""AW"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C7").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""RP"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C8").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""Y"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C9").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DA"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C10").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DS"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""HL"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""NA"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""RD"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""SC"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""AR"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C11").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""Y"",'" & termStart1 & "'!$H:$H,""IS"")"
.Range("C12").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""IS"")"
.Range("C13").Formula = "=SUM(C5:C12)"
.Range("C16").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=0"",'" & termStart1 & "'!$F:$F,""<8"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C17").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=8"",'" & termStart1 & "'!$F:$F,""<15"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C18").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=15"",'" & termStart1 & "'!$F:$F,""<22"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C19").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=22"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C20").Formula = "=SUM(C16:C19)"
.Range("G5").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""IP"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart2 & "'!$D:$D,""ID"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS""))"
.Range("G6").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DM"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""AW"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G7").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""RP"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G8").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""Y"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G9").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DA"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G10").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DS"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""HL"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""NA"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""RD"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""SC"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""AR"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G11").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""Y"",'" & termStart2 & "'!$H:$H,""IS"")"
.Range("G12").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""IS"")"
.Range("G13").Formula = "=SUM(G5:G12)"
.Range("G16").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=0"",'" & termStart2 & "'!$F:$F,""<8"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G17").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=8"",'" & termStart2 & "'!$F:$F,""<15"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G18").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=15"",'" & termStart2 & "'!$F:$F,""<22"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G19").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=22"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G20").Formula = "=SUM(G16:G19)"
.Range("K5").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""IP"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart3 & "'!$D:$D,""ID"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS""))"
.Range("K6").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DM"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""AW"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K7").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""RP"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K8").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""Y"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K9").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DA"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K10").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DS"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""HL"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""NA"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""RD"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""SC"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""AR"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K11").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""Y"",'" & termStart3 & "'!$H:$H,""IS"")"
.Range("K12").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""IS"")"
.Range("K13").Formula = "=SUM(K5:K12)"
.Range("K16").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=0"",'" & termStart3 & "'!$F:$F,""<8"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K17").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=8"",'" & termStart3 & "'!$F:$F,""<15"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K18").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=15"",'" & termStart3 & "'!$F:$F,""<22"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K19").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=22"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K20").Formula = "=SUM(K16:K19)"
.Range("C24").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""IP"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart4 & "'!$D:$D,""ID"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS""))"
.Range("C25").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DM"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""AW"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C26").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""RP"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C27").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""Y"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C28").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DA"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C29").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DS"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""HL"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""NA"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""RD"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""SC"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""AR"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C30").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""Y"",'" & termStart4 & "'!$H:$H,""IS"")"
.Range("C31").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""IS"")"
.Range("C32").Formula = "=SUM(C24:C31)"
.Range("C35").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=0"",'" & termStart4 & "'!$F:$F,""<8"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C36").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=8"",'" & termStart4 & "'!$F:$F,""<15"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C37").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=15"",'" & termStart4 & "'!$F:$F,""<22"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C38").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=22"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C39").Formula = "=SUM(C35:C38)"
.Range("G24").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""IP"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart5 & "'!$D:$D,""ID"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS""))"
.Range("G25").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DM"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""AW"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G26").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""RP"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G27").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""Y"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G28").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DA"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G29").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DS"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""HL"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""NA"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""RD"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""SC"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""AR"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G30").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""Y"",'" & termStart5 & "'!$H:$H,""IS"")"
.Range("G31").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""IS"")"
.Range("G32").Formula = "=SUM(G24:G31)"
.Range("G35").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=0"",'" & termStart5 & "'!$F:$F,""<8"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G36").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=8"",'" & termStart5 & "'!$F:$F,""<15"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G37").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=15"",'" & termStart5 & "'!$F:$F,""<22"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G38").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=22"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G39").Formula = "=SUM(G35:G38)"
.Range("K24").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""IP"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart6 & "'!$D:$D,""ID"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS""))"
.Range("K25").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DM"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""AW"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K26").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""RP"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K27").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""Y"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K28").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DA"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K29").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DS"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""HL"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""NA"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""RD"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""SC"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""AR"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K30").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""Y"",'" & termStart6 & "'!$H:$H,""IS"")"
.Range("K31").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""IS"")"
.Range("K32").Formula = "=SUM(K24:K31)"
.Range("K35").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=0"",'" & termStart6 & "'!$F:$F,""<8"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K36").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=8"",'" & termStart6 & "'!$F:$F,""<15"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K37").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=15"",'" & termStart6 & "'!$F:$F,""<22"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K38").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=22"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K39").Formula = "=SUM(K35:K38)"
.Range("D5").Formula = "=C5/C$13"
.Range("D6").Formula = "=C6/C$13"
.Range("D7").Formula = "=C7/C$13"
.Range("D8").Formula = "=C8/C$13"
.Range("D9").Formula = "=C9/C$13"
.Range("D10").Formula = "=C10/C$13"
.Range("D11").Formula = "=C11/C$13"
.Range("D12").Formula = "=C12/C$13"
.Range("D16").Formula = "=C16/C$20"
.Range("D17").Formula = "=C17/C$20"
.Range("D18").Formula = "=C18/C$20"
.Range("D19").Formula = "=C19/C$20"
.Range("H5").Formula = "=G5/G$13"
.Range("H6").Formula = "=G6/G$13"
.Range("H7").Formula = "=G7/G$13"
.Range("H8").Formula = "=G8/G$13"
.Range("H9").Formula = "=G9/G$13"
.Range("H10").Formula = "=G10/G$13"
.Range("H11").Formula = "=G11/G$13"
.Range("H12").Formula = "=G12/G$13"
.Range("H16").Formula = "=G16/G$20"
.Range("H17").Formula = "=G17/G$20"
.Range("H18").Formula = "=G18/G$20"
.Range("H19").Formula = "=G19/G$20"
.Range("L5").Formula = "=K5/K$13"
.Range("L6").Formula = "=K6/K$13"
.Range("L7").Formula = "=K7/K$13"
.Range("L8").Formula = "=K8/K$13"
.Range("L9").Formula = "=K9/K$13"
.Range("L10").Formula = "=K10/K$13"
.Range("L11").Formula = "=K11/K$13"
.Range("L12").Formula = "=K12/K$13"
.Range("L16").Formula = "=K16/K$20"
.Range("L17").Formula = "=K17/K$20"
.Range("L18").Formula = "=K18/K$20"
.Range("L19").Formula = "=K19/K$20"
.Range("D24").Formula = "=C24/C$32"
.Range("D25").Formula = "=C25/C$32"
.Range("D26").Formula = "=C26/C$32"
.Range("D27").Formula = "=C27/C$32"
.Range("D28").Formula = "=C28/C$32"
.Range("D29").Formula = "=C29/C$32"
.Range("D30").Formula = "=C30/C$32"
.Range("D31").Formula = "=C31/C$32"
.Range("D35").Formula = "=C35/C$39"
.Range("D36").Formula = "=C36/C$39"
.Range("D37").Formula = "=C37/C$39"
.Range("D38").Formula = "=C38/C$39"
.Range("H24").Formula = "=G24/G$32"
.Range("H25").Formula = "=G25/G$32"
.Range("H26").Formula = "=G26/G$32"
.Range("H27").Formula = "=G27/G$32"
.Range("H28").Formula = "=G28/G$32"
.Range("H29").Formula = "=G29/G$32"
.Range("H30").Formula = "=G30/G$32"
.Range("H31").Formula = "=G31/G$32"
.Range("H35").Formula = "=G35/G$39"
.Range("H36").Formula = "=G36/G$39"
.Range("H37").Formula = "=G37/G$39"
.Range("H38").Formula = "=G38/G$39"
.Range("L24").Formula = "=K24/K$32"
.Range("L25").Formula = "=K25/K$32"
.Range("L26").Formula = "=K26/K$32"
.Range("L27").Formula = "=K27/K$32"
.Range("L28").Formula = "=K28/K$32"
.Range("L29").Formula = "=K29/K$32"
.Range("L30").Formula = "=K30/K$32"
.Range("L31").Formula = "=K31/K$32"
.Range("L35").Formula = "=K35/K$39"
.Range("L36").Formula = "=K36/K$39"
.Range("L37").Formula = "=K37/K$39"
.Range("L38").Formula = "=K38/K$39"
.Range("D:D, H:H, L:L").NumberFormat = "0%"
.Range("B5:D5, F5:H5, J5:L5, B24:D24, F24:H24, J24:L24").Interior.Color = RGB(255, 255, 204)
.Range("B6:D6, F6:H6, J6:L6, B25:D25, F25:H25, J25:L25").Interior.Color = RGB(204, 255, 102)
.Range("B7:D7, F7:H7, J7:L7, B26:D26, F26:H26, J26:L26").Interior.Color = RGB(153, 255, 153)
.Range("B8:D8, F8:H8, J8:L8, B27:D27, F27:H27, J27:L27").Interior.Color = RGB(51, 204, 51)
.Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Interior.Color = RGB(0, 51, 0)
.Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Font.Color = RGB(255, 255, 255)
.Range("B11:D12, F11:H12, J11:L12, B30:D31, F30:H31, J30:L31").Interior.Color = RGB(191, 191, 191)
.Range("B16:D19, F16:H19, J16:L19, B35:D38, F35:H38, J35:L38").Interior.Color = RGB(153, 255, 153)ASKER
termstart1 and so forth was the name of the sheet which was the date chosen
With xlSheet7
.Range("B3").Value = termStart1
.Range("F3").Value = termStart2
.Range("J3").Value = termStart3
.Range("B22").Value = termStart4
.Range("F22").Value = termStart5
.Range("J22").Value = termStart6
.Range("B4, F4, J4, B23, F23, J23").Value = "Status"
.Range("B5, F5, J5, B24, F24, J24").Value = "Incomplete"
.Range("B6, F6, J6, B25, F25, J25").Value = "Ready To Package - Special Processing"
.Range("B7, F7, J7, B26, F26, J26").Value = "Ready To Package"
.Range("B8, F8, J8, B27, F27, J27").Value = "Awarded"
.Range("B9, F9, J9, B28, F28, J28").Value = "Declined Aid"
.Range("B10, F10, J10, B29, F29, J29").Value = "Not Eligible"
.Range("B11, F11, J11, B30, F30, J30").Value = "Inactive Awarded"
.Range("B12, F12, J12, B31, F31, J31").Value = "Inactive Not Awarded"
.Range("B13, F13, J13, B32, F32, J32, B20, F20, J20, B39, F39, J39").Value = "Total"
.Range("B15, F15, J15, B34, F34, J34").Value = "Active Students Days RP"
.Range("B16, F16, J16, B35, F35, J35").Value = "0 - 7"
.Range("B17, F17, J17, B36, F36, J36").Value = "'8 - 14"
.Range("B18, F18, J18, B37, F37, J37").Value = "15 - 21"
.Range("B19, F19, J19, B38, F38, J38").Value = "22+"
.Range("C4, G4, K4, C23, G23, K23, C15, G15, K15, C34, G34, K34").Value = "Students"
.Range("D4, H4, L4, D23, H23, L23, D15, H15, L15, D34, H34, L34").Value = "Percent"
.Range("C5").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""IP"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart1 & "'!$D:$D,""ID"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS""))"
.Range("C6").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DM"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""AW"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C7").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""RP"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C8").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""Y"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C9").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DA"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C10").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DS"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""HL"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""NA"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""RD"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""SC"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""AR"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C11").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""Y"",'" & termStart1 & "'!$H:$H,""IS"")"
.Range("C12").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""IS"")"
.Range("C13").Formula = "=SUM(C5:C12)"
.Range("C16").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=0"",'" & termStart1 & "'!$F:$F,""<8"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C17").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=8"",'" & termStart1 & "'!$F:$F,""<15"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C18").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=15"",'" & termStart1 & "'!$F:$F,""<22"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C19").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=22"",'" & termStart1 & "'!$H:$H,""<>IS"")"
.Range("C20").Formula = "=SUM(C16:C19)"
.Range("G5").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""IP"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart2 & "'!$D:$D,""ID"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS""))"
.Range("G6").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DM"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""AW"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G7").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""RP"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G8").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""Y"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G9").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DA"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G10").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DS"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""HL"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""NA"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""RD"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""SC"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""AR"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G11").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""Y"",'" & termStart2 & "'!$H:$H,""IS"")"
.Range("G12").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""IS"")"
.Range("G13").Formula = "=SUM(G5:G12)"
.Range("G16").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=0"",'" & termStart2 & "'!$F:$F,""<8"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G17").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=8"",'" & termStart2 & "'!$F:$F,""<15"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G18").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=15"",'" & termStart2 & "'!$F:$F,""<22"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G19").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=22"",'" & termStart2 & "'!$H:$H,""<>IS"")"
.Range("G20").Formula = "=SUM(G16:G19)"
.Range("K5").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""IP"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart3 & "'!$D:$D,""ID"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS""))"
.Range("K6").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DM"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""AW"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K7").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""RP"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K8").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""Y"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K9").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DA"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K10").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DS"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""HL"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""NA"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""RD"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""SC"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""AR"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K11").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""Y"",'" & termStart3 & "'!$H:$H,""IS"")"
.Range("K12").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""IS"")"
.Range("K13").Formula = "=SUM(K5:K12)"
.Range("K16").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=0"",'" & termStart3 & "'!$F:$F,""<8"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K17").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=8"",'" & termStart3 & "'!$F:$F,""<15"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K18").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=15"",'" & termStart3 & "'!$F:$F,""<22"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K19").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=22"",'" & termStart3 & "'!$H:$H,""<>IS"")"
.Range("K20").Formula = "=SUM(K16:K19)"
.Range("C24").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""IP"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart4 & "'!$D:$D,""ID"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS""))"
.Range("C25").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DM"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""AW"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C26").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""RP"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C27").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""Y"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C28").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DA"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C29").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DS"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""HL"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""NA"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""RD"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""SC"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""AR"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C30").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""Y"",'" & termStart4 & "'!$H:$H,""IS"")"
.Range("C31").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""IS"")"
.Range("C32").Formula = "=SUM(C24:C31)"
.Range("C35").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=0"",'" & termStart4 & "'!$F:$F,""<8"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C36").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=8"",'" & termStart4 & "'!$F:$F,""<15"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C37").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=15"",'" & termStart4 & "'!$F:$F,""<22"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C38").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=22"",'" & termStart4 & "'!$H:$H,""<>IS"")"
.Range("C39").Formula = "=SUM(C35:C38)"
.Range("G24").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""IP"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart5 & "'!$D:$D,""ID"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS""))"
.Range("G25").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DM"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""AW"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G26").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""RP"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G27").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""Y"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G28").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DA"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G29").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DS"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""HL"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""NA"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""RD"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""SC"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""AR"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G30").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""Y"",'" & termStart5 & "'!$H:$H,""IS"")"
.Range("G31").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""IS"")"
.Range("G32").Formula = "=SUM(G24:G31)"
.Range("G35").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=0"",'" & termStart5 & "'!$F:$F,""<8"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G36").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=8"",'" & termStart5 & "'!$F:$F,""<15"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G37").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=15"",'" & termStart5 & "'!$F:$F,""<22"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G38").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=22"",'" & termStart5 & "'!$H:$H,""<>IS"")"
.Range("G39").Formula = "=SUM(G35:G38)"
.Range("K24").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""IP"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart6 & "'!$D:$D,""ID"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS""))"
.Range("K25").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DM"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""AW"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K26").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""RP"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K27").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""Y"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K28").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DA"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K29").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DS"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""HL"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""NA"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""RD"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""SC"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""AR"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K30").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""Y"",'" & termStart6 & "'!$H:$H,""IS"")"
.Range("K31").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""IS"")"
.Range("K32").Formula = "=SUM(K24:K31)"
.Range("K35").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=0"",'" & termStart6 & "'!$F:$F,""<8"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K36").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=8"",'" & termStart6 & "'!$F:$F,""<15"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K37").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=15"",'" & termStart6 & "'!$F:$F,""<22"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K38").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=22"",'" & termStart6 & "'!$H:$H,""<>IS"")"
.Range("K39").Formula = "=SUM(K35:K38)"
.Range("D5").Formula = "=C5/C$13"
.Range("D6").Formula = "=C6/C$13"
.Range("D7").Formula = "=C7/C$13"
.Range("D8").Formula = "=C8/C$13"
.Range("D9").Formula = "=C9/C$13"
.Range("D10").Formula = "=C10/C$13"
.Range("D11").Formula = "=C11/C$13"
.Range("D12").Formula = "=C12/C$13"
.Range("D16").Formula = "=C16/C$20"
.Range("D17").Formula = "=C17/C$20"
.Range("D18").Formula = "=C18/C$20"
.Range("D19").Formula = "=C19/C$20"
.Range("H5").Formula = "=G5/G$13"
.Range("H6").Formula = "=G6/G$13"
.Range("H7").Formula = "=G7/G$13"
.Range("H8").Formula = "=G8/G$13"
.Range("H9").Formula = "=G9/G$13"
.Range("H10").Formula = "=G10/G$13"
.Range("H11").Formula = "=G11/G$13"
.Range("H12").Formula = "=G12/G$13"
.Range("H16").Formula = "=G16/G$20"
.Range("H17").Formula = "=G17/G$20"
.Range("H18").Formula = "=G18/G$20"
.Range("H19").Formula = "=G19/G$20"
.Range("L5").Formula = "=K5/K$13"
.Range("L6").Formula = "=K6/K$13"
.Range("L7").Formula = "=K7/K$13"
.Range("L8").Formula = "=K8/K$13"
.Range("L9").Formula = "=K9/K$13"
.Range("L10").Formula = "=K10/K$13"
.Range("L11").Formula = "=K11/K$13"
.Range("L12").Formula = "=K12/K$13"
.Range("L16").Formula = "=K16/K$20"
.Range("L17").Formula = "=K17/K$20"
.Range("L18").Formula = "=K18/K$20"
.Range("L19").Formula = "=K19/K$20"
.Range("D24").Formula = "=C24/C$32"
.Range("D25").Formula = "=C25/C$32"
.Range("D26").Formula = "=C26/C$32"
.Range("D27").Formula = "=C27/C$32"
.Range("D28").Formula = "=C28/C$32"
.Range("D29").Formula = "=C29/C$32"
.Range("D30").Formula = "=C30/C$32"
.Range("D31").Formula = "=C31/C$32"
.Range("D35").Formula = "=C35/C$39"
.Range("D36").Formula = "=C36/C$39"
.Range("D37").Formula = "=C37/C$39"
.Range("D38").Formula = "=C38/C$39"
.Range("H24").Formula = "=G24/G$32"
.Range("H25").Formula = "=G25/G$32"
.Range("H26").Formula = "=G26/G$32"
.Range("H27").Formula = "=G27/G$32"
.Range("H28").Formula = "=G28/G$32"
.Range("H29").Formula = "=G29/G$32"
.Range("H30").Formula = "=G30/G$32"
.Range("H31").Formula = "=G31/G$32"
.Range("H35").Formula = "=G35/G$39"
.Range("H36").Formula = "=G36/G$39"
.Range("H37").Formula = "=G37/G$39"
.Range("H38").Formula = "=G38/G$39"
.Range("L24").Formula = "=K24/K$32"
.Range("L25").Formula = "=K25/K$32"
.Range("L26").Formula = "=K26/K$32"
.Range("L27").Formula = "=K27/K$32"
.Range("L28").Formula = "=K28/K$32"
.Range("L29").Formula = "=K29/K$32"
.Range("L30").Formula = "=K30/K$32"
.Range("L31").Formula = "=K31/K$32"
.Range("L35").Formula = "=K35/K$39"
.Range("L36").Formula = "=K36/K$39"
.Range("L37").Formula = "=K37/K$39"
.Range("L38").Formula = "=K38/K$39"
.Range("D:D, H:H, L:L").NumberFormat = "0%"
.Range("B5:D5, F5:H5, J5:L5, B24:D24, F24:H24, J24:L24").Interior.Color = RGB(255, 255, 204)
.Range("B6:D6, F6:H6, J6:L6, B25:D25, F25:H25, J25:L25").Interior.Color = RGB(204, 255, 102)
.Range("B7:D7, F7:H7, J7:L7, B26:D26, F26:H26, J26:L26").Interior.Color = RGB(153, 255, 153)
.Range("B8:D8, F8:H8, J8:L8, B27:D27, F27:H27, J27:L27").Interior.Color = RGB(51, 204, 51)
.Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Interior.Color = RGB(0, 51, 0)
.Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Font.Color = RGB(255, 255, 255)
.Range("B11:D12, F11:H12, J11:L12, B30:D31, F30:H31, J30:L31").Interior.Color = RGB(191, 191, 191)
.Range("B16:D19, F16:H19, J16:L19, B35:D38, F35:H38, J35:L38").Interior.Color = RGB(153, 255, 153)SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. This is the issue that I am having. I am creating report where I want the user to pick as many terms and date as they need. Once the user chooses their dates, the report will generate data in separate tabs for each selected date. Each tab will be name the date that the user chooses. After the user finish choosing their dates the I am trying to get the code to create a separate tab named summary. I create the program before, I hard coded the dates, so that is why I am making changes. Does this help understand my question more?