How to: Turn Summary Sheet into detailed Sheets (multiple tabs)

Run the macro Inspection_Summaries in the attached file.

Checksheet.xlsm

Format the Template worksheet's Notes field (A57) with red font.

Sub Inspection_Summaries()
    
    Dim wsIST As Worksheet: Set wsIST = Sheets("IS Template")   'Inspection Summary Template sheet
    Dim cell As Range, i As Integer
    
    Application.ScreenUpdating = False
    With Sheets("Checksheet")
        For Each cell In .Range("K7", .Range("K" & Rows.Count).End(xlUp))
            If .Range("B" & cell.Row).Value <> "" Then
                i = 0
                wsIST.Copy After:=Sheets(Sheets.Count)  'Copy template
                ActiveSheet.Name = .Range("A" & cell.Row).Value & " - " & .Range("B" & cell.Row).Value
                Range("F2").Value = .Range("C3").Value               'Inspector Name
                Range("F3").Value = .Range("F" & cell.Row).Value     'QC Date
                Range("F4").Value = .Range("D" & cell.Row).Value     'Foreman
                Range("F5").Value = .Range("G" & cell.Row).Value     'Completed Date
                Range("F6").Value = .Range("B" & cell.Row).Value     'Structure No.
                Range("O2").Value = "TD" & .Range("C" & cell.Row).Value     'TD#
                Range("O3").Value = .Range("E" & cell.Row).Value     'Client
                Range("O4").Value = "Metro West"                     'Region ???
                Range("O5").Value = .Range("I3").Value               'District
                If .Range("H" & cell.Row).Value <> "" Then               'FAIL
                    Range("D16").Value = .Range("K" & cell.Row).Value    'Comments
                    Range("D18").Value = .Range("H" & cell.Row).Value    '1st Problem Code
                End If
            ElseIf .Range("H" & cell.Row).Value <> "" And i <= 5 Then  'Next problem code
                i = i + 1   'Problem code counter
                Range("D16").Offset(i * 8).Value = .Range("K" & cell.Row).Value   'Comments
                Range("D18").Offset(i * 8).Value = .Range("H" & cell.Row).Value   'Next Problem Code
            Else
                Range("A57").Value = .Range("K" & cell.Row).Value   'Notes
            End If
        Next cell
        .Select
    End With
    Application.ScreenUpdating = True
    
End Sub

Originally Posted by JPWRana

AlphaFrog, I have been testing the macro for a while, and there is a few hiccups that I wouldn't have noticed if I didn't test it for a while.

When I have (for example) more than one row of "notes", it only copies over into the new sheet 1 of those rows. Can you fix that so that it doesn't matter which order the (up to 3 different line items of notes) notes are?

Sub Inspection_Summaries()
    
    Dim wsIST As Worksheet: Set wsIST = Sheets("IS Template")   'Inspection Summary Template sheet
    Dim cell As Range, i As Integer, j As Integer
    
    Application.ScreenUpdating = False
    With Sheets("Checksheet")
        For Each cell In .Range("K7", .Range("K" & Rows.Count).End(xlUp))
            If .Range("B" & cell.Row).Value <> "" Then
                i = 0: j = 0
                wsIST.Copy After:=Sheets(Sheets.Count)  'Copy template
                ActiveSheet.Name = .Range("A" & cell.Row).Value & " - " & .Range("B" & cell.Row).Value
                Range("F2").Value = .Range("C3").Value               'Inspector Name
                Range("F3").Value = .Range("F" & cell.Row).Value     'QC Date
                Range("F4").Value = .Range("D" & cell.Row).Value     'Foreman
                Range("F5").Value = .Range("G" & cell.Row).Value     'Completed Date
                Range("F6").Value = .Range("B" & cell.Row).Value     'Structure No.
                Range("O2").Value = "TD" & .Range("C" & cell.Row).Value     'TD#
                Range("O3").Value = .Range("E" & cell.Row).Value     'Client
                Range("O4").Value = "Metro West"                     'Region ???
                Range("O5").Value = .Range("I3").Value               'District
                If .Range("H" & cell.Row).Value <> "" Then               'FAIL
                    Range("D16").Value = .Range("K" & cell.Row).Value    'Comments
                    Range("D18").Value = .Range("H" & cell.Row).Value    '1st Problem Code
                End If
            ElseIf .Range("H" & cell.Row).Value <> "" And i <= 5 Then  'Next problem code
                i = i + 1   'Problem code counter
                Range("D16").Offset(i * 8).Value = .Range("K" & cell.Row).Value   'Comments
                Range("D18").Offset(i * 8).Value = .Range("H" & cell.Row).Value   'Next Problem Code
            Else
                Range("A57").Offset(j).Value = .Range("K" & cell.Row).Value   'Notes
                j = j + 1
            End If
        Next cell
        .Select
    End With
    Application.ScreenUpdating = True
    
End Sub