Solved: Transpose selected cells and place result on other sheet

[LLEW1]

The following works well as a transpose routine, except for where it places the result - currently placing the transposed cells on the source sheet. I need place the transposed cells onto another sheet, same workbook - called "Sheet 2" - so think I will need to have the Cells(i, "BD") point to "Sheet 2" somehow, but can be same cells in the second sheet.

The code is triggered by button on the source page.

Appreciate any suggestions.


[vba]Sub Button11_Click()
'Sub Transpose1()
Dim lRow As Long
lRow = Cells(Rows.Count, "BS").End(xlUp).Row
i = 10 'row in column to commence paste
For j = 8 To lRow '** to ** , start point to end point
For k = 1 To 1
Cells(i, "BD") = Cells(j, k) 'Paste column
i = i + 1
Next k
Next j


End Sub[/vba]

[GTO]

Greetings LLEW1,

Well, as written, you are not transposing anything. See, to Transpose would mean for instance, if you had values in A1, B1, C1, D1 and E1, and you Transposed this range of a row into a range of a column - let's say, F1:F5. Does that make sense?

Now, as to what I (a bit guessing) am reading your code to be doing (or what you want to do that is), you want to take the values from a sheet in the range of A8:Axx (xx representing the last row in column that has a value), and you want to place (copy) these values into a like sized/shaped range starting in BD10, but you'd like it to be in BD10:BDxx of Sheet 2.

Now presuming the source range and that 'lRow = Cells(Rows.Count, "BS").End(xlUp).Row' are from Sheet 1, try:
[vba]
Option Explicit
Sub Button11_Click()
'Sub Transpose1()
Dim i As Long
Dim j As Long
Dim k As Long
Dim lRow As Long

lRow = ThisWorkbook.Worksheets("Sheet 1").Cells(Rows.Count, "BS").End(xlUp).Row
i = 10 'row in column to commence paste
For j = 8 To lRow '** to ** , start point to end point
For k = 1 To 1
ThisWorkbook.Worksheets("Sheet 2").Cells(i, "BD").Value = _
ThisWorkbook.Worksheets("Sheet 1").Cells(j, k) 'Paste column
i = i + 1
Next k
Next j
End Sub
[/vba]
Now please try that in a copy of your workbook (wb), and it that works, we can simplify it a bit, I do believe.

Hope this helps,

Mark

[LLEW1]

Hi GTO,

thats been of great help - does it for me; solved my block and works well - thanks for your help here.

All the best.

[GTO]

You bet and happy to help :-) If you would, at the top of your thread, under the Thread Tools button is a "Solved" option, so that others don't check to see if help is still needed.

Have a great day (or actually night),

Mark

[LLEW1]

Listed as Solved now, I believe...and thanks again GTO...catch you next time.