macro to insert headers

solved........

modified the following vba code...........

Option Explicit

Sub Cap_Table()
 
Application.ScreenUpdating = False

    Dim i As Integer, lstrow1 As Integer, TotRow As Integer, TotCol As Integer, lstrow2 As Integer
    Dim ws1 As Worksheet
    Dim rngX As Range
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set ws1 = ActiveSheet
 
    Set rngX = ws1.Range("A:A").Find("Grand Total", LookAt:=xlWhole)
    If Not rngX Is Nothing Then
       
        ws1.Rows(Mid(rngX.Address, InStr(2, rngX.Address, "$") + 1, 3) + 1 & ":65536").Delete
    End If
 
    Set rngX = ws1.Range("A:A").Find("Labels", LookAt:=xlPart)
    If Not rngX Is Nothing Then
        TotRow = Mid(rngX.Address, InStr(2, rngX.Address, "$") + 1, 3)
        TotCol = ws1.Cells(Mid(rngX.Address, InStr(2, rngX.Address, "$") + 1, 3), "IV").End(xlToLeft).Column
        lstrow1 = ws1.Cells(65536, "A").End(xlUp).Row
        ws1.Rows(TotRow & ":" & lstrow1).Copy
        ws1.Range("A" & lstrow1 + 6).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                         :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.NumberFormat = "0.00"
        lstrow2 = ws1.Cells(65536, "A").End(xlUp).Row
        ws1.Cells(lstrow1 + 4, 1) = "Current Capacity Data"
        ws1.Cells(lstrow1 + 6, 1) = "HOFs"
        ws1.Cells(lstrow1 + 6, 4) = "Temp"
        For i = 1 To TotCol
            ws1.Cells(lstrow1 + 4, 1).Interior.Color = RGB(250, 192, 144)
            ws1.Cells(lstrow1 + 4, 1).Font.Bold = True
            ws1.Cells(lstrow1 + 4, 2).Interior.Color = RGB(250, 192, 144)
            ws1.Cells(lstrow1 + 4, 2).Font.Bold = True
            ws1.Cells(lstrow1 + 6, i).Interior.ColorIndex = 41
            ws1.Cells(lstrow1 + 6, i).Font.Bold = True
            ws1.Cells(lstrow2, i).Interior.ColorIndex = 41
            ws1.Cells(lstrow2, i).Font.Bold = True
        Next
 
    End If
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
End Sub

Can you show me how does you want to see your output..??

Hi Suarbs

The output should be add the TABLE ONE: BREAKDOWN header above each of the tables. i.e. find Ice cream and miss one row and add the header TABLE ONE:

Repeat the steps for other tables.....find banana and miss one row and add header TABLE TWO: BREAKDOWN

etc.............the attachment shows the finished product........so if rows 2,17,27 and 41 where not there that would be the starting point.......ADD theses rows we have the finished product.

thanks once again