VBA to Create sheets from Template AND update Summary Sheet formulas for new sheets

M

MattUCF415

New Member
Joined
Jan 16, 2019
Messages
1
Hey guys,

I am trying to make a cost template easier on the end user and want to have 1 or 2 macro buttons to create the cost file. I have the listed VBA code below to reference a list and create worksheets based on the quantity and titles in the list and then create hyperlinks to the newly created worksheets and other worksheets in the file. Where I am running into trouble is when I want to update the formulas in my summary sheet....any advice on how to merge the commands together and point the second VBA to the correct sheet (I used the recorder for that one)? My code in VBA1 is based off copying from a template, so all of the new worksheets from the list are the same and the cells I reference in VBA2 are the same, only difference is worksheet name (if that helps).

I don't necessarily need VBA2 in the same structure, just the only formula/referencing solution I came up with based on my limited VBA experience. Ideally, VBA2 would be able to loop into VBA1, then update only newly created worksheets from the list in VBA1, and then exclude any extra lines from the list...ex I want to create 5 new worksheets instead of the max of 15. Summary tab has 15 pre-filled rows for the max of 15 new worksheets from the list.

VBA 1: the create new sheets from a list and hyperlink list

Code: 
Sub UpdateTemplateSheets()
    ' Update sheets in list created from a template
    '
    ' Input: List on master sheet, template sheet
    ' Output: Updated sheet from template for each item in list
    '
    Dim wsInitial As Worksheet
    Dim wsMaster As Worksheet
    Dim wsTemp As Worksheet
    Dim lVisibility As XlSheetVisibility
    Dim strSheetName As String
    Dim rIndex As Long
    Dim i As Long


    On Error GoTo Safe_Exit
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    ' Application.Calculation = xlCalculationManual


    Set wsInitial = ActiveSheet
    Set wsMaster = Sheets("Set up")
    Set wsTemp = Sheets("CLIN Template")


    lVisibility = wsTemp.Visible          ' In case template sheet is hidden
    wsTemp.Visible = xlSheetVisible


    For rIndex = 2 To wsMaster.Cells(Rows.Count, "K").End(xlUp).Row
        ' Ensure valid sheet name
        strSheetName = wsMaster.Cells(rIndex, "K").Text
        For i = 1 To 7
            strSheetName = Replace(strSheetName, Mid(":\/?*[]", i, 1), " ")
        Next i
        strSheetName = Trim(Left(WorksheetFunction.Trim(strSheetName), 31))


        ' Ensure sheet name doesn't already exist
        If Not Evaluate("IsRef('" & strSheetName & "'!A1)") Then
            wsTemp.Copy after:=Sheets(Sheets.Count)
            With Sheets(Sheets.Count)
                .name = strSheetName
                Sheets(strSheetName).Move Before:=Sheets("CLIN End")
            End With
        End If
        With Sheets(strSheetName)
            .Range("B59").Value = rIndex * 16 + 1        ' Update template block option row
        End With
    Next rIndex


Safe_Exit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    'Application.Calculation = xlCalculationAutomatic
    wsInitial.Activate
    wsTemp.Visible = lVisibility  ' Set template sheet to its original visible state
    
    Dim objSheet As Worksheet


    For Each objSheet In ActiveWorkbook.Worksheets
        If ActiveSheet.name <> objSheet.name Then
           ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & objSheet.name & "'" & "!A1", TextToDisplay:=objSheet.name
           ActiveCell.Offset(1, 0).Select
           ActiveCell.EntireColumn.AutoFit
        End If
    Next objSheet




End Sub

VBA #2 : update formulas from new worksheets into summary tab (establish the links)
Code: 
Sub Update_CLIN_Summary_Sheet()
'
' Update_CLIN_Summary_Sheet Macro
' This recorded Macro updates the CLIN summary tab formulas to reference the newly created tabs.   **Run this AFTER creasting the new CLIN tabs**
'


'


    Range("J8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R28C21,""-""))"
    Range("J9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R28C21,""-""))"
    Range("J10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R28C21,""-""))"
    Range("J11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R28C21,""-""))"
    Range("J12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R28C21,""-""))"
    Range("J13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R28C21,""-""))"
    Range("J13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R28C21,""-""))"
    Range("J14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R28C21,""-""))"
    Range("J15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R28C21,""-""))"
    Range("J16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R28C21,""-""))"
    Range("J17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R28C21,""-""))"
    Range("J18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R28C21,""-""))"
    Range("J19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R28C21,""-""))"
    Range("J20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R28C21,""-""))"
    Range("J21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R28C21,""-""))"
    Range("J22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R28C21,""-""))"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = "=++IF(RC2="""","""",IFERROR('CLIN 01'!R29C21,""-""))"
    Range("K9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R29C21,""-""))"
    Range("K10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R29C21,""-""))"
    Range("K11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R29C21,""-""))"
    Range("K12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R29C21,""-""))"
    Range("K13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R29C21,""-""))"
    Range("K14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R29C21,""-""))"
    Range("K15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R29C21,""-""))"
    Range("K16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R29C21,""-""))"
    Range("K17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R29C21,""-""))"
    Range("K18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R29C21,""-""))"
    Range("K19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R29C21,""-""))"
    Range("K20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R29C21,""-""))"
    Range("K21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R29C21,""-""))"
    Range("K22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R29C21,""-""))"
    Range("L8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R30C21,""-""))"
    Range("L9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R30C21,""-""))"
    Range("L10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R30C21,""-""))"
    Range("L11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R30C21,""-""))"
    Range("L12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R30C21,""-""))"
    Range("L13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R30C21,""-""))"
    Range("L14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R30C21,""-""))"
    Range("L15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R30C21,""-""))"
    Range("L16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R30C21,""-""))"
    Range("L17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R30C21,""-""))"
    Range("L18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R30C21,""-""))"
    Range("L19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R30C21,""-""))"
    Range("L20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R30C21,""-""))"
    Range("L21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R30C21,""-""))"
    Range("L22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R30C21,""-""))"
    Range("M8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R36C166,""-""))"
    Range("M9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R36C166,""-""))"
    Range("M10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R36C166,""-""))"
    Range("M11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R36C166,""-""))"
    Range("M12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R36C166,""-""))"
    Range("M13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R36C166,""-""))"
    Range("M14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R36C166,""-""))"
    Range("M15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R36C166,""-""))"
    Range("M16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R36C166,""-""))"
    Range("M17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R36C166,""-""))"
    Range("M18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R36C166,""-""))"
    Range("M19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R36C166,""-""))"
    Range("M20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R36C166,""-""))"
    Range("M21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R36C166,""-""))"
    Range("M22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R36C166,""-""))"
    Range("N8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R50C166,""-""))"
    Range("N9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R50C166,""-""))"
    Range("N10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R50C166,""-""))"
    Range("N11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R50C166,""-""))"
    Range("N12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R50C166,""-""))"
    Range("N13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R50C166,""-""))"
    Range("N14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R50C166,""-""))"
    Range("N15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R50C166,""-""))"
    Range("N16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R50C166,""-""))"
    Range("N17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R50C166,""-""))"
    Range("N18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R50C166,""-""))"
    Range("N19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R50C166,""-""))"
    Range("N20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R50C166,""-""))"
    Range("N21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R50C166,""-""))"
    Range("N22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R50C166,""-""))"
    Range("O8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R51C166,""-""))"
    Range("O9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R51C166,""-""))"
    Range("O10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R51C166,""-""))"
    Range("O11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R51C166,""-""))"
    Range("O12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R51C166,""-""))"
    Range("O13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R51C166,""-""))"
    Range("O14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R51C166,""-""))"
    Range("O15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R51C166,""-""))"
    Range("O16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R51C166,""-""))"
    Range("O15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R51C166,""-""))"
    Range("O16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R51C166,""-""))"
    Range("O17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R51C166,""-""))"
    Range("O18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R51C166,""-""))"
    Range("O19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R51C166,""-""))"
    Range("O20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R51C166,""-""))"
    Range("O21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R51C166,""-""))"
    Range("O22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R51C166,""-""))"
    Range("P8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R52C166,""-""))"
    Range("P9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R52C166,""-""))"
    Range("P10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R52C166,""-""))"
    Range("P11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R52C166,""-""))"
    Range("P12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R52C166,""-""))"
    Range("P13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R52C166,""-""))"
    Range("P14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R52C166,""-""))"
    Range("P15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R52C166,""-""))"
    Range("P16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R52C166,""-""))"
    Range("P17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R52C166,""-""))"
    Range("P18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R52C166,""-""))"
    Range("P19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R52C166,""-""))"
    Range("P20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R52C166,""-""))"
    Range("P21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R52C166,""-""))"
    Range("P22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R52C166,""-""))"
    Range("Q8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R53C166,""-""))"
    Range("Q9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R53C166,""-""))"
    Range("Q10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R53C166,""-""))"
    Range("Q11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R53C166,""-""))"
    Range("Q12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R53C166,""-""))"
    Range("Q13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R53C166,""-""))"
    Range("Q14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R53C166,""-""))"
    Range("Q15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R53C166,""-""))"
    Range("Q16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R53C166,""-""))"
    Range("Q17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R53C166,""-""))"
    Range("Q18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R53C166,""-""))"
    Range("Q19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R53C166,""-""))"
    Range("Q20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R53C166,""-""))"
    Range("Q21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R53C166,""-""))"
    Range("Q22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R53C166,""-""))"
    Range("R8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R54C166,""-""))"
    Range("R9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R54C166,""-""))"
    Range("R10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R54C166,""-""))"
    Range("R11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R54C166,""-""))"
    Range("R12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R54C166,""-""))"
    Range("R13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R54C166,""-""))"
    Range("R14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R54C166,""-""))"
    Range("R15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R54C166,""-""))"
    Range("R16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R54C166,""-""))"
    Range("R17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R54C166,""-""))"
    Range("R18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R54C166,""-""))"
    Range("R19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R54C166,""-""))"
    Range("R20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R54C166,""-""))"
    Range("R21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R54C166,""-""))"
    Range("R22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R54C166,""-""))"
    Range("S8").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 01'!R55C166,""-""))"
    Range("S9").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 02'!R55C166,""-""))"
    Range("S10").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 03'!R55C166,""-""))"
    Range("S11").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 04'!R55C166,""-""))"
    Range("S12").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 05'!R55C166,""-""))"
    Range("S13").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 06'!R55C166,""-""))"
    Range("S14").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 07'!R55C166,""-""))"
    Range("S15").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 08'!R55C166,""-""))"
    Range("S16").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 09'!R55C166,""-""))"
    Range("S17").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 10'!R55C166,""-""))"
    Range("S18").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 11'!R55C166,""-""))"
    Range("S19").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 12'!R55C166,""-""))"
    Range("S20").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 13'!R55C166,""-""))"
    Range("S21").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 14'!R55C166,""-""))"
    Range("S22").Select
    ActiveCell.FormulaR1C1 = "=+IF(RC2="""","""",IFERROR('CLIN 15'!R55C166,""-""))"
    Range("V8").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 01'!R66C21,""-"")"
    Range("V9").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 02'!R66C21,""-"")"
    Range("V10").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 03'!R66C21,""-"")"
    Range("V11").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 04'!R66C21,""-"")"
    Range("V12").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 05'!R66C21,""-"")"
    Range("V13").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 06'!R66C21,""-"")"
    Range("V14").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 07'!R66C21,""-"")"
    Range("V15").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 08'!R66C21,""-"")"
    Range("V16").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 09'!R66C21,""-"")"
    Range("V17").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 10'!R66C21,""-"")"
    Range("V18").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 11'!R66C21,""-"")"
    Range("V19").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 12'!R66C21,""-"")"
    Range("V20").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 13'!R66C21,""-"")"
    Range("V21").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 14'!R66C21,""-"")"
    Range("V22").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 15'!R66C21,""-"")"
    Range("W8").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 01'!R65C21,""-"")"
    Range("W9").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 02'!R65C21,""-"")"
    Range("W10").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 03'!R65C21,""-"")"
    Range("W11").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 04'!R65C21,""-"")"
    Range("W12").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 05'!R65C21,""-"")"
    Range("W13").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 06'!R65C21,""-"")"
    Range("W14").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 07'!R65C21,""-"")"
    Range("W15").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 08'!R65C21,""-"")"
    Range("W16").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 09'!R65C21,""-"")"
    Range("W17").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 10'!R65C21,""-"")"
    Range("W18").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 11'!R65C21,""-"")"
    Range("W19").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 12'!R65C21,""-"")"
    Range("W20").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 13'!R65C21,""-"")"
    Range("W21").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 14'!R65C21,""-"")"
    Range("W22").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 15'!R65C21,""-"")"
    Range("X8").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 01'!R67C21,""-"")"
    Range("X9").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 02'!R67C21,""-"")"
    Range("X10").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 03'!R67C21,""-"")"
    Range("X11").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 04'!R67C21,""-"")"
    Range("X12").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 05'!R67C21,""-"")"
    Range("X13").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 06'!R67C21,""-"")"
    Range("X14").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 07'!R67C21,""-"")"
    Range("X15").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 08'!R67C21,""-"")"
    Range("X16").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 09'!R67C21,""-"")"
    Range("X17").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 10'!R67C21,""-"")"
    Range("X18").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 11'!R67C21,""-"")"
    Range("X19").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 12'!R67C21,""-"")"
    Range("X20").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 13'!R67C21,""-"")"
    Range("X21").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 14'!R67C21,""-"")"
    Range("X22").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 15'!R67C21,""-"")"
    Range("Z8").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 01'!R77C21,""-"")"
    Range("Z9").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 02'!R77C21,""-"")"
    Range("Z10").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 03'!R77C21,""-"")"
    Range("Z11").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 04'!R77C21,""-"")"
    Range("Z12").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 05'!R77C21,""-"")"
    Range("Z13").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 06'!R77C21,""-"")"
    Range("Z14").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 07'!R77C21,""-"")"
    Range("Z15").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 08'!R77C21,""-"")"
    Range("Z16").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 09'!R77C21,""-"")"
    Range("Z17").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 10'!R77C21,""-"")"
    Range("Z18").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 11'!R77C21,""-"")"
    Range("Z19").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 12'!R77C21,""-"")"
    Range("Z20").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 13'!R77C21,""-"")"
    Range("Z21").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 14'!R77C21,""-"")"
    Range("Z22").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 15'!R77C21,""-"")"
    Range("AA8").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 01'!R76C21,""-"")"
    Range("AA9").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 02'!R76C21,""-"")"
    Range("AA10").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 03'!R76C21,""-"")"
    Range("AA11").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 04'!R76C21,""-"")"
    Range("AA12").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 05'!R76C21,""-"")"
    Range("AA13").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 06'!R76C21,""-"")"
    Range("AA14").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 07'!R76C21,""-"")"
    Range("AA15").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 08'!R76C21,""-"")"
    Range("AA16").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 09'!R76C21,""-"")"
    Range("AA17").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 10'!R76C21,""-"")"
    Range("AA18").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 11'!R76C21,""-"")"
    Range("AA19").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 12'!R76C21,""-"")"
    Range("AA20").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 13'!R76C21,""-"")"
    Range("AA21").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 14'!R76C21,""-"")"
    Range("AA22").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 15'!R76C21,""-"")"
    Range("AB8").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 01'!R78C21,""-"")"
    Range("AB9").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 02'!R78C21,""-"")"
    Range("AB10").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 03'!R78C21,""-"")"
    Range("AB11").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 04'!R78C21,""-"")"
    Range("AB12").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 05'!R78C21,""-"")"
    Range("AB13").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 06'!R78C21,""-"")"
    Range("AB14").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 07'!R78C21,""-"")"
    Range("AB15").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 08'!R78C21,""-"")"
    Range("AB16").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 09'!R78C21,""-"")"
    Range("AB17").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 10'!R78C21,""-"")"
    Range("AB18").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 11'!R78C21,""-"")"
    Range("AB19").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 12'!R78C21,""-"")"
    Range("AB20").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 13'!R78C21,""-"")"
    Range("AB21").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 14'!R78C21,""-"")"
    Range("AB22").Select
    ActiveCell.FormulaR1C1 = "=+IFERROR('CLIN 15'!R78C21,""-"")"
    Range("AB23").Select
End Sub


Thanks!
-Matt
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You must log in or register to reply here.

Similar threads

K
  • Solved
Index with Multiple Conditions
Replies
2
Views
64
kivikatz
K
P
  • Solved
What will be formula on Search Box as i type data filter accordingly...
2 3
Replies
26
Views
601
pankajgrover
P
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
216
SBExcel123
S
P
  • Solved
Copy column values from another sheet but want to exclude some specific row name completely..
2 3
Replies
23
Views
641
Peter_SSs
Peter_SSs
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
104
vancher19
V

Forum statistics

Threads
1,223,868
Messages
6,175,082
Members
452,611
Latest member
bls2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top