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
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