excel vba - Transpose from rows to column (VBA) -

my question is: how transpose last 4 rows of column g different columns?

i use static code:

worksheets("sheet8").range("a1:a5").copy worksheets("sheet9").range("a1").pastespecial transpose:=true 

but doesn't allow me stay on same sheet.

so trying combine code:

dim r range, n 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 appreciated

untested:

dim c range, v  'find last-used cell in colg set c = cells(rows.count, "g").end(xlup)  c.offset(-3,0) 'starting cell 3 rows above last-used cell...     v = .resize(4,1).value      'get value of 4-row/1-col range below     .resize(4,1).clearcontents  '...then clear range     .resize(1,4).value = application.transpose(v) 'place values in row end