none
VBA formula question - can a worksheet be referred to as a variable in a function? RRS feed

  • Question

  • I am creating a macro to create review logs for my workers. 

    The bi-monthly summary sheets pull data from the previous two logs.

    All sheets are in the same workbook.

    Is there a way in VBA to refer to the sheet names as variables when I know the cell I want, but I don't know what the sheet name will be ahead of time?

     Record Macro gave me this:

    Range("D5").Select
        ActiveCell.FormulaR1C1 = "='Month2'!R[-15]C[-1]"
    

    Which is fine, except that I am already manually creating those formulas.  I am trying to save time by creating them automatically and have everything working but these summary formulas.  Ideally what I want is something like:

    Range("D5").Select
        ActiveCell.FormulaR1C1 = "='ReviewMonth2'!R[-15]C[-1]"
    
    ' and then the next month
    
    Range("D5").Select
        ActiveCell.FormulaR1C1 = "='ReviewMonth3'!R[-15]C[-1]"

    ReviewMonthX is already defined as a variable and is used to name the sheets.  I want to use that variable and add the cell reference needed so that when I check the Summary sheet, the value of the cell correctly updates.  

    Is this possible, or do I need to resign myself to doing it be hand?  

    Thanks,

    Mark

    Tuesday, May 3, 2016 10:42 PM

Answers

  • Try...

    ActiveCell.FormulaR1C1 = "='" & ReviewMonthX & "'!R[-15]C[-1]"

    Although, there's no need to select the cell first.  Here's an example...

    Range("D20").FormulaR1C1 = "='" & ReviewMonthX & "'!R[-15]C[-1]"

    Hope this helps!


    Domenic Tamburino

    Microsoft MVP - Excel

    xl-central.com - "For Your Microsoft Excel Solutions"



    Tuesday, May 3, 2016 11:31 PM

All replies

  • Try...

    ActiveCell.FormulaR1C1 = "='" & ReviewMonthX & "'!R[-15]C[-1]"

    Although, there's no need to select the cell first.  Here's an example...

    Range("D20").FormulaR1C1 = "='" & ReviewMonthX & "'!R[-15]C[-1]"

    Hope this helps!


    Domenic Tamburino

    Microsoft MVP - Excel

    xl-central.com - "For Your Microsoft Excel Solutions"



    Tuesday, May 3, 2016 11:31 PM
  • That looks like what I am after, but when i try this:

    Range("C5").FormulaR1C1 = "='" & F1name & "'!R[-2]C[3]"

    I get a Run-time error 1004: Application-defined or object-defined error.

    Wednesday, May 4, 2016 7:21 PM
  • Make sure that the worksheet actually exists within the active workbook, and that there are no spelling mistakes or extra spaces.

    What value is being assigned to F1name?


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Wednesday, May 4, 2016 7:34 PM
  • I understand that this code is pretty sloppy - but it works ;)

    ' Determine initial period dates & Sheet names
        IntervalType = "d"       ' "d" specifies days as interval
        StartPeriod = InputBox("Enter First Date of Review Period")
        EndNumber = 29                                                  ' last day of review period
        StartNumber = 30                                                ' first day of next period - fed into same procedure for 2nd thru n months. 
        Edate = DateAdd(IntervalType, EndNumber, StartPeriod)           ' string
        Sdate = DateAdd(IntervalType, StartNumber, StartPeriod)
        Edated = Edate                                                  ' string as date
        SDated2 = Sdate
        Range("A1").Value = Edated
        Range("A2").Value = StartPeriod
        Range("A1").NumberFormat = "[$-409]mmm-dd;@"
        Range("A2").NumberFormat = "[$-409]mmm-dd;@"
        ShBDate = Range("A2").Text                                      ' Correctly formatted begin date
        ShEDate = Range("A1").Text                                      ' Correctly formatted end date
        F1name = "Case Action " & ShBDate & " thru " & ShEDate
        F2name = "Case Review " & ShBDate & " thru " & ShEDate
        Range("A1:A2").ClearContents
        
        ' Month One
        Sheets("Sheet1").Select
        Sheets("Sheet1").Name = F1name
        Sheets.Add After:=ActiveSheet
        Sheets("Sheet2").Select
        Sheets("Sheet2").Name = F2name

    So F1name, in this case is (seed date of 5/2): Case Action May-02 thru May-31

    I have made these *name variables Public (Public F1name as String etc.), because I have put the formatting and formulas for each type of sheet into a separate module. As far as spacing goes, I read 

    Range("C5").FormulaR1C1 = "='" & F1name & "'!R[-2]C[3]"

    as Range("C5").FormulaR1C1 (space) = (space) "='" (space) & (space) F1name (space) & "'!R[-2]C[3]"

    Is that right?


    Wednesday, May 4, 2016 7:52 PM
  • Can you please confirm which line is causing the error?

    Also, try stepping through the code, inputting a date, and stop when you get to this line...

    Range("C5").FormulaR1C1 = "='" & F1name & "'!R[-2]C[3]"

    Then move your cursor over the variable F1name.  What value has been assigned to it?  And what date did you enter in the inputbox?


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"



    Wednesday, May 4, 2016 8:19 PM
  • In the module where F1Name is set, it reads as "Case Action May-02 thru May-31"

    The part of the macro that sets the sheet names works perfectly. 

    In the module where I want to use it as a variable in the formula it reads the same. 

    When I right click and choose definition, it brings me back to where it is initially declared as "Public F1name as String"

    The date entered into the inputbox is 5/2

    Thanks,

    Mark

    Wednesday, May 4, 2016 9:25 PM
  • Hi Mark,

    Have your issue been resolved? I made a test with Range("C5").FormulaR1C1 = "='" & F1name & "'!R[-2]C[3]", it works correctly.

    If your issue still exist, I suggest you share us a simple excel file which could reproduce your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, May 5, 2016 7:08 AM
  • No, I am still getting that error, but now when I hover over F1name, it reads as F1name = "".

    I don't see a way to attach a file, but here is a simplified (only dealing with months 1 & 2) version of the code I have.

    Module1, which determines dates and names sheets:

    Option Explicit Public ShBDate As String Public ShBDate2 As String Public ShEDate As String Public ShEDate2 As String Public F1name As String Public F2name As String Public M2F1name As String Public M2F2name As String Public M2F3name As String ' Format date in MM/DD format ' .NumberFormat = "[$-409]mmm-dd;@" Sub An_Amazing_Log_Creator() Dim StartPeriod As Date Dim StartPeriod2 As Date Dim StartPeriod3 As Date Dim IntervalType As String Dim EndNumber As Integer Dim EndNumber2 As Integer Dim EndNumber3 As Integer Dim StartNumber As Integer Dim StartNumber2 As Integer Dim Edate As String Dim Edate2 As String Dim Sdate As String Dim Sdate2 As String Dim Sdate3 As String Dim SDated2 As Date Dim SDated3 As Date Dim Edated As Date Dim Edated2 As Date ' Determine initial period dates & Sheet names IntervalType = "d" ' "d" specifies days as interval StartPeriod = InputBox("Enter First Date of Review Period") EndNumber = 29 ' last day of review period StartNumber = 30 ' first day of next period Edate = DateAdd(IntervalType, EndNumber, StartPeriod) ' string Sdate = DateAdd(IntervalType, StartNumber, StartPeriod) Edated = Edate ' string as date SDated2 = Sdate Range("A1").Value = Edated Range("A2").Value = StartPeriod Range("A1").NumberFormat = "[$-409]mmm-dd;@" Range("A2").NumberFormat = "[$-409]mmm-dd;@" ShBDate = Range("A2").Text ' Correctly formatted begin date ShEDate = Range("A1").Text ' Correctly formatted end date F1name = "Case Action " & ShBDate & " thru " & ShEDate F2name = "Case Review " & ShBDate & " thru " & ShEDate Range("A1:A2").ClearContents ' Month One Sheets("Sheet1").Select Sheets("Sheet1").Name = F1name Sheets.Add After:=ActiveSheet Sheets("Sheet2").Select Sheets("Sheet2").Name = F2name ' Determine 2nd month dates and Sheet names IntervalType = "d" ' "d" specifies days as interval StartPeriod2 = SDated2 ' EndNumber2 = 29 StartNumber2 = 30 Edate2 = DateAdd(IntervalType, EndNumber2, StartPeriod2) Sdate3 = DateAdd(IntervalType, StartNumber2, StartPeriod2) Edated2 = Edate2 SDated3 = Sdate3 Range("A1").Value = Edated2 Range("A2").Value = StartPeriod2 Range("A1").NumberFormat = "[$-409]mmm-dd;@" Range("A2").NumberFormat = "[$-409]mmm-dd;@" ShBDate2 = Range("A2").Text ShEDate2 = Range("A1").Text M2F1name = "Case Action " & ShBDate2 & " thru " & ShEDate2 M2F2name = "Case Review " & ShBDate2 & " thru " & ShEDate2 M2F3name = "Stat Summary " & ShBDate & " thru " & ShEDate2 Range("A1:A2").ClearContents ' Month 2 Sheets.Add After:=ActiveSheet Sheets("Sheet3").Select Sheets("Sheet3").Name = M2F1name Sheets.Add After:=ActiveSheet Sheets("Sheet4").Select Sheets("Sheet4").Name = M2F2name Sheets.Add After:=ActiveSheet Sheets("Sheet5").Select Sheets("Sheet5").Name = M2F3name

    An_Log_Filler

    End Sub

    Module2 which formats the case action sheets I need to pull data from:

    Sub CaseAction()
    '
    ' CaseActionFormatting Macro
    ' Case Action Sheet Formatting Recorded.
    '
    
    '
        Columns("A:A").ColumnWidth = 1.14
        Range("B1:G1").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        Range("B1:G1").Select
        ActiveCell.FormulaR1C1 = "Case Action Type Summary"
        Range("B2").Select
        ActiveCell.FormulaR1C1 = "#"
        Range("B3").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("B4").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("B3:G4").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Range("B4:G4").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("B3:G4").Select
        Selection.AutoFill Destination:=Range("B3:G13"), Type:=xlFillDefault
        Range("B3:G13").Select
        Range("B2:G2").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("B2:G2").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Range("B2").Select
        Columns("B:B").ColumnWidth = 2.71
        Range("B2").Select
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Range("C2:D2").Select
        Selection.Merge True
        Range("C3:D3").Select
        Selection.Merge True
        Range("C2:D3").Select
        Selection.AutoFill Destination:=Range("C2:D13"), Type:=xlFillDefault
        Range("C2:D13").Select
        Range("C2:D2").Select
        ActiveCell.FormulaR1C1 = "Case Action Type"
        Range("E2").Select
        ActiveCell.FormulaR1C1 = "#Needed"
        Range("E2").Select
        ActiveCell.FormulaR1C1 = "# Needed"
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "# Completed"
        Range("G2").Select
        ActiveCell.FormulaR1C1 = "# Remaining"
        Range("H2").Select
        Columns("E:E").ColumnWidth = 11.43
        Columns("F:F").ColumnWidth = 15.29
        Columns("G:G").ColumnWidth = 17.86
        Range("B14:D14").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        Range("B14:D14").Select
        ActiveCell.FormulaR1C1 = "Total Cases"
        Range("B14:G14").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Range("E3").Select
        ActiveCell.FormulaR1C1 = "12"
        Range("E4").Select
        ActiveCell.FormulaR1C1 = "6"
        Range("E5").Select
        ActiveCell.FormulaR1C1 = "15"
        Range("E6").Select
        ActiveCell.FormulaR1C1 = "18"
        Range("E7").Select
        ActiveCell.FormulaR1C1 = "15"
        Range("E8").Select
        ActiveCell.FormulaR1C1 = "7"
        Range("E9").Select
        ActiveCell.FormulaR1C1 = "7"
        Range("E10").Select
        ActiveCell.FormulaR1C1 = "4"
        Range("E11").Select
        ActiveCell.FormulaR1C1 = "4"
        Range("E12").Select
        ActiveCell.FormulaR1C1 = "4"
        Range("E13").Select
        ActiveCell.FormulaR1C1 = "4"
        Range("E14").Select
        ActiveCell.FormulaR1C1 = "96"
        Range("C3:D3").Select
        ActiveCell.FormulaR1C1 = "New Application - PND1 to PND2"
        Range("C4:D4").Select
        ActiveCell.FormulaR1C1 = "PND2"
        Range("C5:D5").Select
        ActiveCell.FormulaR1C1 = "Recert (SNAP w/without HC)"
        Range("C6:D6").Select
        ActiveCell.FormulaR1C1 = "Healthcare Renewal (MAGI or Non-MAGI)"
        Range("C7:D7").Select
        ActiveCell.FormulaR1C1 = "CSR"
        Range("C8:D8").Select
        ActiveCell.FormulaR1C1 = "Income Changes"
        Range("C9:D9").Select
        ActiveCell.FormulaR1C1 = "Other Changes"
        Range("C10:D10").Select
        ActiveCell.FormulaR1C1 = "TIKL Dail"
        Range("C11:D11").Select
        ActiveCell.FormulaR1C1 = "PEPR Dail"
        Range("C12:D12").Select
        ActiveCell.FormulaR1C1 = "HIRE Dail"
        Range("C13:D13").Select
        ActiveCell.FormulaR1C1 = "Other Dail"
        Range("C2:D2").Select
        Columns("D:D").ColumnWidth = 11.71
        Range("C2:D13").Select
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
        End With
        Range("C4:D4").Select
        Columns("D:D").ColumnWidth = 13.57
        ActiveWindow.SmallScroll Down:=6
        ' Application.Left = 1199.5
        ' Application.Top = -110
        ' Application.Width = 1230
        ' Application.Height = 705
        ' Windows("Cert Log master - Jan2016.xlsx").Activate
    '     Windows("Book1").Activate
        Range("B16:K360").Select
        ActiveWindow.SmallScroll Down:=-348
        Range("B16").Select
        ActiveCell.FormulaR1C1 = "#"
        Range("C16").Select
        ActiveCell.FormulaR1C1 = "Reviewer Action"
        Range("D16").Select
        ActiveCell.FormulaR1C1 = "Date"
        Range("E16").Select
        ActiveCell.FormulaR1C1 = "In-Basket"
        Range("F16").Select
        ActiveCell.FormulaR1C1 = "Case #"
        Range("G16").Select
        ActiveCell.FormulaR1C1 = "Case/Curr (SNAP/CASH/HC)"
        Range("H16").Select
        ActiveCell.FormulaR1C1 = "Case Action Type"
        Range("I16").Select
        ActiveCell.FormulaR1C1 = _
            "Worker Comments (Unique notes, Mentor/Buddy/HSS help note, etc)"
        Range("I16").Select
        ActiveCell.FormulaR1C1 = _
            "Worker Comments (Unique notes, Mentor/Buddy/HSS help note, etc.)"
        Range("J16").Select
        ActiveCell.FormulaR1C1 = "# of Work Items Removed from ECF"
        Range("K16").Select
        ActiveCell.FormulaR1C1 = "Reviewer Action Note"
        Range("K16").Select
        Columns("K:K").ColumnWidth = 30.86
        Columns("J:J").ColumnWidth = 12.29
        Columns("I:I").ColumnWidth = 21.43
        Columns("H:H").ColumnWidth = 16.86
        Columns("G:G").ColumnWidth = 21.29
        Range("B16:K16").Select
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        With Selection.Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        Range("B16").Select
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Range("B17").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("B18").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("B17:K17").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("B17:K18").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.AutoFill Destination:=Range("B17:K360"), Type:=xlFillDefault
        Range("B17:K360").Select
        ActiveWindow.SmallScroll Down:=-342
        Range("B16:K16").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Range("B16").Select
        Columns("B:B").EntireColumn.AutoFit
        ActiveWindow.SmallScroll Down:=-12
        Rows("14:14").RowHeight = 30
        Range("B14:G14").Select
        With Selection
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .ShrinkToFit = False
            .ReadingOrder = xlContext
        End With
        ActiveWindow.SmallScroll Down:=3
        Range("I14:J14").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Range("I14").Select
        ActiveCell.FormulaR1C1 = "Number of Work Items Removed From ECF"
        Range("I14").Select
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        With Selection.Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        ActiveWindow.SmallScroll Down:=-15
        ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 443.25, 42, 183.75 _
            , 105).Select
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
            "Once you have had 3 SNAP New Applications & 2 SNAP PND2's reviewed, then you can approve your remaining SNAP applications and PND2's."
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 133).ParagraphFormat _
            .FirstLineIndent = 0
        With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 41).Font
            .Bold = msoTrue
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
            .Fill.ForeColor.TintAndShade = 0
            .Fill.ForeColor.Brightness = 0
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 12
            .Name = "+mn-lt"
        End With
        With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(42, 27).Font
            .BaselineOffset = 0
            .Bold = msoTrue
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
            .Fill.ForeColor.TintAndShade = 0
            .Fill.ForeColor.Brightness = 0
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 12
            .Name = "+mn-lt"
        End With
        With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(69, 65).Font
            .BaselineOffset = 0
            .Bold = msoTrue
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
            .Fill.ForeColor.TintAndShade = 0
            .Fill.ForeColor.Brightness = 0
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 12
            .Italic = msoTrue
            .Name = "+mn-lt"
        End With
        ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
        Selection.ShapeRange.Shadow.Type = msoShadow21
        Application.CommandBars("Format Object").Visible = False
        Range("B2:G2").Select
        Selection.Font.Bold = True
        Selection.Font.Italic = True
        Range("C2:D2").Select
        Columns("D:D").ColumnWidth = 15.29
     
     
      
       
        Range("F3").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[14]C[2]:R[357]C[2],RC[-3])"
        Range("F4").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[13]C[2]:R[356]C[2],RC[-3])"
        Range("F5").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[12]C[2]:R[355]C[2],RC[-3])"
        Range("F6").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[11]C[2]:R[354]C[2],RC[-3])"
        Range("F7").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[10]C[2]:R[353]C[2],RC[-3])"
        Range("F8").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[9]C[2]:R[352]C[2],RC[-3])"
        Range("F9").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[8]C[2]:R[351]C[2],RC[-3])"
        Range("F10").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[7]C[2]:R[350]C[2],RC[-3])"
        Range("F11").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[6]C[2]:R[349]C[2],RC[-3])"
        Range("F12").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[5]C[2]:R[348]C[2],RC[-3])"
        Range("F13").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[4]C[2]:R[347]C[2],RC[-3])"
        Range("F14").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
        Range("G3").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
        Range("G3").Select
        Selection.AutoFill Destination:=Range("G3:G14"), Type:=xlFillDefault
        Range("G3:G14").Select
        Range("G14,G12,G10,G8,G6,G4").Select
        Range("G4").Activate
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        ActiveWindow.SmallScroll Down:=6
        Range("C17:C360").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:= _
            "Yes - M, Yes - O, Not Reviewable - M, Not Reviewable - O"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Range("D17:D360").Select
        Selection.NumberFormat = "m/d/yyyy"
        Range("F17:F360").Select
        Selection.NumberFormat = "0"
        Range("G17:G360").Select
        ActiveWindow.SmallScroll Down:=-333
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:= _
            "SNAP, CASH, SNAP/CASH, HC, SNAP/HC, CASH/HC, SNAP/CASH/HC"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Range("H17:H360").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$C$3:$C$13"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        ActiveWindow.SmallScroll Down:=12
        Columns("H:H").EntireColumn.AutoFit
        Range("H18").Select
        ActiveWindow.SmallScroll Down:=-12
        Range("H18").Select
        Selection.ClearContents
        Range("H17").Select
        Selection.ClearContents
        Columns("H:H").Select
        Range("H13").Activate
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Range("G17").Select
        Selection.ClearContents
        Columns("C:C").Select
        Range("C15").Activate
        With Selection
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .ShrinkToFit = False
            .ReadingOrder = xlContext
        End With
        Selection.ColumnWidth = 11.14
        Range("C17").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=-3
    
    '  Merge
        
        Columns("B:B").EntireColumn.AutoFit
        Columns("F:F").EntireColumn.AutoFit
        Columns("C:C").ColumnWidth = 14
        Columns("G:G").ColumnWidth = 16
        Columns("H:H").ColumnWidth = 20
        Columns("I:I").ColumnWidth = 26
        Range("J14").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[3]C:R[346]C)"
        Range("B1:G1").Select
        With Selection.Font
            .Name = "Calibri"
            .Size = 20
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        Selection.Font.Bold = True
        ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
        With Selection.ShapeRange.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent2
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0.8000000119
            .Transparency = 0
            .Solid
        End With
        Range("D17:J360").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
        Range("D17").Select
    
        
    End Sub
    

    Module 3, which formats the Case Review sheets I'm not including because if I can get it to work with the Case Action sheets, I should be able to make it work there too.

    Module 4 which has the Stat Summary sheet:

    Option Explicit
    
    Sub StatSumM1()
    '
    ' StatSumFormat Macro
    '
        ' Formats first stat sum sheet.
        ' For ex: if seed date is 5/2, the first stat sum sheet is period
        ' 5/2 thru 6/30.
    
    
    '
    
        Range("A1:F1").Select
        Selection.Merge True
        Range("A1:F1").Select
        ActiveCell.Value = "60 Day Cumulative Summary for " + ShBDate + " thru " + ShEDate2
        Range("H1").Select
        Range("A1:I1").Select
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
    
        Range("A3:B3").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        Range("A3:B3").Select
        ActiveCell.FormulaR1C1 = "Quantity Summary"
        Range("A4:H16").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        Range("A4").Select
        ActiveCell.FormulaR1C1 = "#"
        Range("B4").Select
        ActiveCell.FormulaR1C1 = "Case Action Type"
        Range("C4").Select
        ActiveCell.Value = ShBDate + " thru " + ShEDate
        Range("D4").Select
        ActiveCell.Value = ShBDate2 + " thru " + ShEDate2
        Range("E4").Select
        ActiveCell.FormulaR1C1 = "60 Day Total"
        Range("F4").Select
        ActiveCell.FormulaR1C1 = "60 Day Standard"
        Range("G4").Select
        ActiveCell.FormulaR1C1 = "Variety Percentage"
        Range("H4").Select
        ActiveCell.FormulaR1C1 = "60 Day Variety Standard"
        Range("G4").Select
        ActiveCell.FormulaR1C1 = "60 Day Variety Percentage"
        Range("A4:H4").Select
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlCenter
            .WrapText = True
            .Orientation = 0
            .ReadingOrder = xlContext
        End With
        Columns("B:B").ColumnWidth = 18.43
        Columns("C:C").EntireColumn.AutoFit
        Columns("C:C").ColumnWidth = 11.29
        Columns("C:C").ColumnWidth = 12.86
        Columns("D:D").ColumnWidth = 12.14
        Columns("E:E").ColumnWidth = 11
        Columns("F:F").ColumnWidth = 9.71
        Columns("G:G").ColumnWidth = 9.86
        Columns("G:G").EntireColumn.AutoFit
        Columns("G:G").ColumnWidth = 10.43
        Selection.Font.Bold = True
        Range("A5").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("A6").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("A5:H5").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("A5:H6").Select
        Selection.AutoFill Destination:=Range("A5:H15"), Type:=xlFillDefault
        Range("A5:H15").Select
        Range("B5").Select
        ActiveCell.FormulaR1C1 = "New Application - PND1 to PND2"
        Range("B6").Select
        ActiveCell.FormulaR1C1 = "PND2"
        Range("B7").Select
        ActiveCell.FormulaR1C1 = "Recert (Cash or SNAP w/without HC)"
        Range("B8").Select
        ActiveCell.FormulaR1C1 = "Health Care Renewal (MAGI or Non-MAGI)"
        Range("B9").Select
        ActiveCell.FormulaR1C1 = "CSR"
        Range("B10").Select
        ActiveCell.FormulaR1C1 = "Income Changes"
        Range("B11").Select
        ActiveCell.FormulaR1C1 = "Other Changes"
        Range("B12").Select
        ActiveCell.FormulaR1C1 = "TIKL Dail"
        Range("B13").Select
        ActiveCell.FormulaR1C1 = "PEPR Dail"
        Range("B14").Select
        ActiveCell.FormulaR1C1 = "HIRE Dail"
        Range("B15").Select
        ActiveCell.FormulaR1C1 = "Other DAIL"
        Range("B5:B15").Select
        Range("B15").Activate
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .ReadingOrder = xlContext
        End With
        Columns("B:B").ColumnWidth = 25.57
        Columns("B:B").ColumnWidth = 30.57
        Columns("B:B").ColumnWidth = 35
        Columns("B:B").ColumnWidth = 32.57
        Columns("B:B").ColumnWidth = 36.14
        Columns("B:B").ColumnWidth = 38.29
        Range("B16").Select
        ActiveCell.FormulaR1C1 = "Total Cases"
        Range("C16").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
        Range("D16").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
        Range("E16").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        Range("F16").Select
        ActiveCell.FormulaR1C1 = "192"
        Range("B16").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
        End With
        Range("B16:F16").Select
        Selection.Font.Bold = True
        Rows("7:7").EntireRow.AutoFit
        Rows("8:8").EntireRow.AutoFit
        Rows("8:8").EntireRow.AutoFit
        Rows("7:7").EntireRow.AutoFit
        Rows("8:8").RowHeight = 14.25
        Rows("8:8").RowHeight = 15
        Range("E5").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        Range("E5").Select
        Selection.AutoFill Destination:=Range("E5:E15"), Type:=xlFillValues
        Range("E5:E15").Select
        Range("F5").Select
        ActiveCell.FormulaR1C1 = "24"
        Range("F6").Select
        ActiveCell.FormulaR1C1 = "12"
        Range("F7").Select
        ActiveCell.FormulaR1C1 = "30"
        Range("F8").Select
        ActiveCell.FormulaR1C1 = "36"
        Range("F9").Select
        ActiveCell.FormulaR1C1 = "30"
        Range("F10").Select
        ActiveCell.FormulaR1C1 = "14"
        Range("F11").Select
        ActiveCell.FormulaR1C1 = "14"
        Range("F12").Select
        ActiveCell.FormulaR1C1 = "8"
        Range("F13").Select
        ActiveCell.FormulaR1C1 = "8"
        Range("F14").Select
        ActiveCell.FormulaR1C1 = "8"
        Range("F15").Select
        ActiveCell.FormulaR1C1 = "8"
        Range("G5").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],0)"
        Range("G5").Select
        Selection.AutoFill Destination:=Range("G5:G15"), Type:=xlFillDefault
        Range("G5:G15").Select
        Range("G15").Select
        Range("A18:B18").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        ActiveCell.FormulaR1C1 = "Quality Summary"
        Range("C18").Select
        ActiveCell.FormulaR1C1 = "# of Cases Reviewed"
        Range("C18").Select
        Range("C18:D18").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        Range("H18").Select
        ActiveCell.FormulaR1C1 = "Case Review Summary"
        Range("H18:L18").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        Range("A18:L18").Select
        Selection.Font.Bold = True
        Selection.Font.Underline = xlUnderlineStyleSingle
        Range("A19:F31").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        Range("A19").Select
        ActiveCell.FormulaR1C1 = "#"
        Range("B19").Select
        ActiveCell.FormulaR1C1 = ""
        Range("F19").Select
        ActiveCell.FormulaR1C1 = "Reviews Needed"
        Range("A4:E15").Select
        Selection.Copy
        Range("A19").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.AutoFill Destination:=Range("A19:F30"), Type:=xlFillFormats
        Range("A19:F30").Select
        Range("F20").Select
        ActiveCell.FormulaR1C1 = "6"
        Range("F21").Select
        ActiveCell.FormulaR1C1 = "4"
        Range("F22").Select
        ActiveCell.FormulaR1C1 = "6"
        Range("F23").Select
        ActiveCell.FormulaR1C1 = "8"
        Range("F24").Select
        ActiveCell.FormulaR1C1 = "6"
        Range("F25").Select
        ActiveCell.FormulaR1C1 = "6"
        Range("F26").Select
        ActiveCell.FormulaR1C1 = "3"
        Range("F27").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("F28").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("F29").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("F30").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("F31").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
        Range("F25").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("F26").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("B31").Select
        ActiveCell.FormulaR1C1 = "Total Reviews"
        Range("F31").Select
        Selection.AutoFill Destination:=Range("C31:F31"), Type:=xlFillDefault
        Range("C31:F31").Select
        Range("B31").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .ReadingOrder = xlContext
        End With
        Selection.Font.Bold = True
        Range("H19:L22").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        Range("H20:L20").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("H22:L22").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("H19:I19").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        Range("H19:I19").Select
        ActiveCell.FormulaR1C1 = "Summary"
        Range("J19").Select
        ActiveCell.Value = ShBDate + " thru " + ShEDate
        Range("K19").Select
        ActiveCell.Value = ShBDate2 + " thru " + ShEDate2
        Range("L19").Select
        ActiveCell.FormulaR1C1 = "60 Day Total"
        Range("M19").Select
        Columns("J:J").EntireColumn.AutoFit
        Columns("K:K").EntireColumn.AutoFit
        Columns("L:L").EntireColumn.AutoFit
        Range("H19:L19").Select
        Selection.Font.Bold = True
        Range("H20:I22").Select
        Selection.Merge True
        Range("H20:I20").Select
        ActiveCell.FormulaR1C1 = "Total Cases Reviewed"
        Range("J20").Select
        Columns("I:I").EntireColumn.AutoFit
        Columns("I:I").EntireColumn.AutoFit
        Columns("I:I").EntireColumn.AutoFit
        Columns("I:I").ColumnWidth = 9.71
        Columns("I:I").ColumnWidth = 11
        Range("H21:I21").Select
        ActiveCell.FormulaR1C1 = "Total Eligibility Errors"
        Range("H22:I22").Select
        ActiveCell.FormulaR1C1 = "Accuracy"
        Range("G6,G8,G10,G12,G14").Select
        Range("G14").Activate
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("G5:H15").Select
        Selection.NumberFormat = "0%"
        Range("H5:H15").Select
        Selection.ClearContents
        ActiveCell.FormulaR1C1 = "75%"
        Range("H6").Select
        ActiveCell.FormulaR1C1 = "75%"
        Range("H7").Select
        ActiveCell.FormulaR1C1 = "75%"
        Range("H8").Select
        ActiveCell.FormulaR1C1 = "75%"
        Range("H9").Select
        ActiveCell.FormulaR1C1 = "75%"
        Range("H10").Select
        ActiveCell.FormulaR1C1 = "75%"
        Range("H11").Select
        ActiveCell.FormulaR1C1 = "100%"
        Range("H12").Select
        ActiveCell.FormulaR1C1 = "100%"
        Range("H13").Select
        ActiveCell.FormulaR1C1 = "100%"
        Range("H14").Select
        ActiveCell.FormulaR1C1 = "100%"
        Range("H15").Select
        ActiveCell.FormulaR1C1 = "100%"
        Range("L20").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        Range("L21").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        Range("L22").Select
        ActiveCell.FormulaR1C1 = "=IFERROR((RC[-2]+RC[-1])/2,0)"
        Range("J22:L22").Select
        Selection.NumberFormat = "0%"
        Range("H24:J24").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        ActiveCell.FormulaR1C1 = "60 Day Stat Summary"
        Range("H25:J27").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
        Range("H25").Select
        ActiveCell.FormulaR1C1 = "Qualia"
        Range("I25").Select
        ActiveCell.FormulaR1C1 = "Standard"
        Range("J25").Select
        ActiveCell.FormulaR1C1 = "Individual"
        Range("H25:J25").Select
        Selection.Font.Bold = True
        Selection.Font.Underline = xlUnderlineStyleSingle
        Range("H26:J26").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("H26").Select
        ActiveCell.FormulaR1C1 = "Quantity"
        Range("H27").Select
        ActiveCell.FormulaR1C1 = "Quality"
        Range("H26:H27").Select
        Range("H27").Activate
        Selection.Font.Bold = True
        Range("I26").Select
        ActiveCell.FormulaR1C1 = "192"
        Range("I27").Select
        ActiveCell.FormulaR1C1 = "0.85"
        Range("I27:J27").Select
        Selection.NumberFormat = "0%"
        Range("J26").Select
        ActiveCell.FormulaR1C1 = "=R[-10]C[-5]"
        Range("J27").Select
        ActiveCell.FormulaR1C1 = "=R[-5]C[2]"
        Range("A4").Select
    ' format tweaks
        Range("A1:B1").Select
        Selection.Font.Bold = True
        Selection.Font.Underline = xlUnderlineStyleSingle
        With Selection.Font
            .Name = "Calibri"
            .Size = 16
            .Underline = xlUnderlineStyleSingle
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        Range("A4,A19").Select
        Range("A19").Activate
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = True
            .Orientation = 0
            .ReadingOrder = xlContext
        End With
        Rows("23:23").RowHeight = 15
        Range("A1:F1").Select
        ActiveCell.Value = "60 Day Cumulative Summary for " + ShBDate + " thru " + ShEDate2
        
        Range("C5").FormulaR1C1 = "='" & F1name & "'!R[-2]C[3]"
        
        Range("A2").Select
       
    End Sub

    And finally, the macro which calls the other macros

    Sub An_Log_Filler()
        
        ' Runs several "make a Sheets macros"
        Sheets(1).Select
        CaseAction
        Range("C17").Select
            
      '  Sheets(2).Select
      '  CaseReview
      '  Range("B19").Select
         
        Sheets(3).Select
        CaseAction
        Range("C17").Select
        
     '   Sheets(4).Select
     '   CaseReview
     '   Range("B19").Select
        
        Sheets(5).Select
        StatSumM1
        
       
        Sheets(1).Select
        
        
            
     
    End Sub

    I hope this helps.

    Thanks again for all of the assistance.

    Best,

    Mark

    Thursday, May 5, 2016 2:18 PM
  • If I stick everything into 1 module, it works. 

    I'm not sure why it does not work if the Subs are in different modules, since the sheet names are public variables.

    Thursday, May 5, 2016 3:38 PM
  • Hi Mark,

    >>I'm not sure why it does not work if the Subs are in different modules, since the sheet names are public variables.

    Since your original issue about VBA formula have been resolved, if you still have issues about variables in different modules, I suggest you post a new thread for this, and then we could focus on this special issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, May 6, 2016 2:25 AM