adding 1st row as header

[jangir72]

With the help of mrexcel (user: Jamtay317) I could find following codes working well for me, but I want to add 1st row as header that remains same mean that either not clear with clear command or copy paste header in 1st row. I don't know how to do it. Could anybody help me:

Sub test()
Dim j As Integer, k As Integer, r As Range
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
For j = 2 To .Range("a1").End(xlDown).Row
k = .Range("a1").End(xlToRight).Column
Set r = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Range(.Cells(j, 1), .Cells(j, "H")).Copy Range(r, r.Offset(1, 0))

Next j
End With
With Worksheets("sheet2")
For j = 2 To Range("A2").End(xlDown).Row
If j Mod 2 = 1 Then
Cells(j, 1) = Cells(j - 1, 1) + 1
Cells(j, 5) = Cells(j - 1, 6)
Cells(j, 6) = Cells(j - 1, 5)
Cells(j, 7) = Cells(j - 1, 8)
Cells(j, 8) = Cells(j - 1, 7)
End If
Next j

End With
End Sub

 

[patel45]

change
Worksheets("sheet2").Cells.Clear
to
Worksheets("sheet2").Rows("2:1000").ClearContents

 

[jangir72]

Thank you.