Sub-routine to generate checksheet associated with the loop in new sheet. There is lookup table that consist of all information for loop to checksheets relationship.

GenerateChecksheetByLoop.bas

Sub GenerateChecksheetByLoop()
    ' Define the source worksheet
    Dim sourceSheet As Worksheet
    Set sourceSheet = ActiveSheet ' You can change this to the specific sheet
    
    ' Define the selected range
    Dim selectedRange As Range
    On Error Resume Next
    Set selectedRange = Application.InputBox("Select the range containing Loop Name and Count", Type:=8)
    On Error GoTo 0
    
    If selectedRange Is Nothing Then
        MsgBox "Operation canceled."
        Exit Sub
    End If
    
    ' Check if "GENTAGS" sheet exists, and delete it if it does
    Dim newSheetName As String
    newSheetName = "GENTAGS"
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(newSheetName).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    ' Create a new worksheet for generated tags
    Dim newSheet As Worksheet
    Set newSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    newSheet.Name = newSheetName
    
    ' Loop through the selected range
    Dim rowIndex As Long
    rowIndex = 0
    Dim loopCell As Range
    For Each loopCell In selectedRange.Columns(1).Cells
        Dim loopName As String
        loopName = loopCell.Value
        
        Dim tagCount As Long
        tagCount = loopCell.Offset(0, 1).Value
        
        ' Find loop name in the lookup table and get associated tag names
        Dim lookupTable As Range
        Set lookupTable = Worksheets("QVD").Range("ALL_QVD")
        
        Dim resultRange As Range
        On Error Resume Next
        Set resultRange = lookupTable.Columns(5).Find(loopName, LookIn:=xlValues, LookAt:=xlWhole)
        On Error GoTo 0
        
        If Not resultRange Is Nothing Then
            ' Find all occurrences of the loop name in the lookup table
            Dim loopRange As Range
            Set loopRange = lookupTable.Columns(5).Find(loopName, LookIn:=xlValues, LookAt:=xlWhole)
            
            ' Loop through each occurrence and add associated tag names to the new sheet
            Dim i As Long
            For i = 1 To tagCount
                Dim tagName As String
                tagName = loopRange.Offset(0, -1).Cells(i, 1).Value
                Dim barcode As String
                barcode = loopRange.Offset(0, -4).Cells(i, 1).Value
                Dim qvdType As String
                qvdType = loopRange.Offset(0, 7).Cells(i, 1).Value
                                
                newSheet.Cells(i + rowIndex, 1).Value = barcode
                newSheet.Cells(i + rowIndex, 2).Value = tagName
                newSheet.Cells(i + rowIndex, 3).Value = loopName
                newSheet.Cells(i + rowIndex, 4).Value = qvdType
            Next i
        Else
            MsgBox "Loop name not found in the lookup table for: " & loopName
        End If
        rowIndex = rowIndex + tagCount
    Next loopCell
    
    ' Autosize columns in the "GeneratedTags" sheet
    newSheet.Columns.AutoFit
    
    MsgBox "Tags generated successfully!"
End Sub