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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You must log in or register to reply here.

Similar threads

L
  • Solved
Help with formula Number serial based on criteria monthly
Replies
4
Views
115
Peter_SSs
Peter_SSs
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
229
SaraWitch
SaraWitch
GMC The Macro Man
  • Question
Clear Data without VBA
2
Replies
12
Views
598
mumps
mumps
F
  • Question
Formula with conditions of 7 days
Replies
9
Views
265
SanjayGMusafir
SanjayGMusafir
R
  • Solved
Index and Match but exclude if on a list
Replies
3
Views
168
Fluff
Fluff

Forum statistics

Threads
1,214,585
Messages
6,120,394
Members
448,957
Latest member
Hat4Life

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