SOLVED

Macro Recording

Iron 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)
Solution

@Excel 

Try this. It avoids selecting cells.

Sub Header()
    Dim c As Long
    Application.ScreenUpdating = False
    If Cells(2, 1).Value <> "" Then
        c = 1
    Else
        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:smiling_face_with_smiling_eyes:
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Excel 

Try this. It avoids selecting cells.

Sub Header()
    Dim c As Long
    Application.ScreenUpdating = False
    If Cells(2, 1).Value <> "" Then
        c = 1
    Else
        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

View solution in original post