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
        Next

Open in new window

cmedTechnical AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I've no idea what your question is.

However, I would suggest replacing lines 87-123 with:

                            With .Borders()
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With

                            With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                                .LineStyle = Excel.XlLineStyle.xlDouble
                            End With

Open in new window


It does the same thing, with much fewer lines.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmedTechnical AnalystAuthor Commented:
@Phillip Burton

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?
0
cmedTechnical AnalystAuthor Commented:
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)

Open in new window

0
cmedTechnical AnalystAuthor Commented:
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)

Open in new window

0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>> 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.

Have those dates in separate cells, and then read them like:
termstart1 = Sheets("InputSheet").cells(1,1)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.