(Solved) – Excel VBA – Transfer a Range in a column in one sheet and transfer it to a row in another sheet

  • by

I am learning VBA and I have encountered this problem. I have this column in one sheet (See image)enter image description here

What I am trying to achieve is to transfer it to another sheet on the same work book. I must appear like in the image below. The values must appear after the first ID column.

enter image description here

I tried to achieve this using the code below after reading and watching videos, but i cant find what is wrong with it. What I am trying to achieve further is to identify the lastrow in Sheet2 and paste the values from Sheet one to the next available row.

Sub Transpose()

Dim SourceSheet As Worksheet
Dim TransferSheet As Worksheet
Dim inRange, outRange As Range
Dim finalrow As Integer
Dim i As Integer

Set SourceSheet = Sheets("Sheet1")
Set TransferSheet = Sheets("Sheet2")

Set inRange = ActiveSheet.Range("B2:B11")

finalrow = TransferSheet.Cells(Rows.Count, 1).End(xlUp).Row 'find last row

For i = 2 To 11
outRange = TransferSheet.Range(Cells(ii, finalrow), Cells(ii, finalrow))
outRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Next i

End Sub

Any assistance will be great.

Leave a Reply

Your email address will not be published. Required fields are marked *