export all sheet name and folowing colum name by identifier id

export_all_sheet_name_with_column.cls

Sub ListSheetNamesInNewWorkbook()
    Dim objWorkbook As Workbook
    Dim objWorksheet As Worksheet

    Set objWorkbook = ActiveWorkbook
    Set objWorksheet = objWorkbook.Sheets("daftar_isi")

    For i = 1 To ThisWorkbook.Sheets.Count
        objWorksheet.Cells(i, 1) = i
        objWorksheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
        
        Debug.Print "_SHEET"
        Debug.Print ThisWorkbook.Sheets(i).Name
        
        'If ThisWorkbook.Sheets(i).Name Like "*DB*" Then
            ThisWorkbook.Sheets(i).Activate
            For Each cell In ThisWorkbook.Sheets(i).Range("A1:A8")
                Debug.Print cell.Value
                If cell.Value Like "* ID*" Or cell.Value Like "*ID *" Then
                    j = 1
                    While Not IsEmpty(ThisWorkbook.Sheets(i).Cells(cell.Row, j))
                    
                        Debug.Print ThisWorkbook.Sheets(i).Cells(cell.Row, j).Value
                        objWorksheet.Cells(i, 2 + j) = ThisWorkbook.Sheets(i).Cells(cell.Row, j).Value
                        j = j + 1
                    Wend
                    Exit For
                                    
                End If
            Next cell
        'End If
        
    Next i

    With objWorksheet
         .Rows(1).Insert
         .Cells(1, 1) = "INDEX"
         .Cells(1, 1).Font.Bold = True
         .Cells(1, 2) = "NAME"
         .Cells(1, 2).Font.Bold = True
         .Columns("A:B").AutoFit
    End With
    
End Sub