Transfer a Range in a column in one sheet to a row in another sheet

0
votes

I have a column in one sheet
enter image description here

I am trying to transfer it to another sheet on the same workbook. It must appear like the image below. The values must appear after the first ID column.
enter image description here

I tried the code below after reading and watching videos. I am further trying to identify the lastrow in Sheet2 and paste the values from Sheet1 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

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

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

'TRANSFER
TransferSheet.Activate
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
2
excelvba
Use Long rather than Integer, inRange needs to be explicitly declared as range not implicitly as Variant. With Dim inRange, outRange As Range only outRange is a range. You need Dim inRange As Range, outRange As Range. You need set when creating reference to Range object e.g. Set outRange = TransferSheet.Range(Cells(ii, finalrow), Cells(ii, finalrow)) and ii is never declared but you are using a loop variable called i - typo? Other than that I am not sure pastespecial will work there either. I would (depending on size of range) read into array use Transpose function and write out with resize – QHarr
Fully qualify cells references with parent sheet names. Use worksheets collection not sheets. You don't need Activesheet and Activate. Qualify your ranges with the appropriate sheet variables. – QHarr
I thanks those who made changes to the question. I will make sure that my succeeding questions are formated in the same way. – Synod
I hope you can show me examples of your first comment if you don't mind, so I can study it based on your sample as well. I have a pretty good idea of what you may mean, but just to make sure I wont make a mistake and also so I can learn proper coding, i prefer to learn from a sample code. Thanks very much – Synod

2 Answers

0
votes

Try this, please:

   Dim SourceSheet As Worksheet, TransferSheet As Worksheet
   Dim rowVal As Variant, nrCol As Long, ColumnLetter As String
   Set SourceSheet = ActiveWorkbook.Sheets("Sheet1")
   Set TransferSheet = ActiveWorkbook.Sheets("Sheet2")
     rowVal = SourceSheet.Range("B2:B11")
     nrCol = UBound(rowVal)
     ColumnLetter = Split(Cells(1, nrCol + 1).Address, "$")(1)
     TransferSheet.Range("B2:" & ColumnLetter & 2) = Application.WorksheetFunction.Transpose(rowVal)

So, the code declares both pages as you did. Then the range in B:B column is included in the rowVal array. The number of columns is defined like Ubound(rowVal) and the Column letter of the sheet to paste the values is determined like ColumnLetter. nrCol + 1 is used because the paste cell will be in B:B column (the second one) and counting does not start from the first column. Then, using Transpose function the array content is pasted in the TransferSheet appropriate row. The range column is built using the previous determined ColumnLetter...

0
votes

Here is an example following on from my comments

Option Explicit
Public Sub MyTranspose()
    'This assumes column to row transpose
    Dim SourceSheet As Worksheet, TransferSheet As Worksheet
    Dim inRange As Range, inRangeValues() As Variant, outRangeValues() As Variant
    Dim finalRow As Long

    Set SourceSheet = ThisWorkbook.Worksheets("Sheet1") 'Assign reference
    Set TransferSheet = ThisWorkbook.Worksheets("Sheet2")
    Set inRange = SourceSheet.Range("B2:B11")
    inRangeValues() = inRange.Value 'generate 2d array
    outRangeValues = Application.Transpose(inRangeValues)

    With TransferSheet 'Hold reference to parent worksheet
        finalRow = .Cells(Rows.Count, 1).End(xlUp).Row 'find last row
        If inRange.Columns.Count > 1 Then '2d array for output
            .Cells(finalRow + 1, 2).Resize(UBound(outRangeValues, 1), UBound(outRangeValues, 2)) = outRangeValues 'Resize according to output array dimensions
        Else '1D array for output
            .Cells(finalRow + 1, 2).Resize(1, UBound(outRangeValues, 1)) = outRangeValues
            End If
    End With
End Sub

These were my comments (plus a bit):

Use Long rather than Integer, inRange needs to be explicitly declared as Range not implicitly as Variant. With Dim inRange, outRange As Range only outRange is a Range. You need Dim inRange As Range, outRange As Range.

You need Set when creating reference to Range object e.g. Set outRange = TransferSheet.Range(Cells(ii, finalrow), Cells(ii, finalrow)); here Cells will refer to currently active sheet and ii is never declared, but you are using a loop variable called i - typo? Other than that I am not sure pastespecial will work there either.

I would (depending on size of inRange as Transpose has a limit and will truncate or complain after that) read into array, use Transpose function and write out with Resize.

Use Worksheets collection not Sheets. Fully qualify cells references with parent sheet names; As you have these in variables just use the appopriate variable names. You don't need Activesheet and Activate this way and thus your code will be less bug prone (explicit sheet reference) and faster (due to not Activating sheet).

Give your sub a different name from the existing VBA method (something better, yet still descriptive, than I have used.