Jun 14 2021 05:37 AM - edited Jun 14 2021 06:58 AM
Hello Everyone,
I have recorded macro(header) in data.
like -
After recording, i run in a another column where my data is available.
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.
Jun 14 2021 06:58 AM
SolutionTry 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
Jun 14 2021 07:34 AM
Jun 14 2021 06:58 AM
SolutionTry 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