Macro Recording

Regular Contributor

Hello Everyone,

I have recorded macro(header) in data.

like - 

Screenshot (3811).png


After recording, i run in a another column where my data is available.

Screenshot (3812).png


But it is not coming where all data are there.


So, what should i write in VBA code, if i run macro or VBA code so header should be there?

overall it means that it should be more dynamic(header) where my my data is available in different sheet and different cells...???


Please help...??


Here is a attached file.

2 Replies
best response confirmed by allyreckerman (Microsoft)


Try this. It avoids selecting cells.

Sub Header()
    Dim c As Long
    Application.ScreenUpdating = False
    If Cells(2, 1).Value <> "" Then
        c = 1
        c = Cells(2, 1).End(xlToRight).Column
    End If
    Cells(1, 1).EntireRow.Insert
    With Cells(1, c).Resize(1, 9)
        .Value = Array("EmpId", "First Name", "Last Name", "Department", _
            "Email", "Ext.", "Location", "Date", "Pay Rate")
        With .Interior
        .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 6299648
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With .Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .Bold = True
        End With
    End With
    Cells(1, c + 3).ColumnWidth = 9.22
    Cells(1, c + 6).ColumnWidth = 9.33
    Application.ScreenUpdating = True
End Sub
It helps! Thank you so much sir