Transpose from rows to column (VBA)

My question is: how to transpose the last four rows of the column G into different columns?

I usually use this static code:

Worksheets("Sheet8").Range("A1:A5").Copy
Worksheets("Sheet9").Range("A1").PasteSpecial Transpose:=True

But it doesn't allow me to stay on the same Sheet.

So I am trying to combine it with this code:

Dim r As Range, N As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
Set r = Cells(N, 1).EntireRow

r.Copy
Cells(N + 1, 1).PasteSpecial Transpose:=True
r.Delete

Before

After

Any help is appreciated

from Recent Questions - Stack Overflow http://ift.tt/29Q20Fn
via https://ifttt.com/ IFTTT