0
Sub transposeVBAS()

Sheets("Sheet1").Range("C2:C12").Copy
Sheets("sheet2").Range("B2").PasteSpecial Transpose:=True

End Sub

I'm facing problem going to next cell for other entries once the transpose is successful.

I know I have got similar question from past, but those codes seems be very big.

halfer
  • 19,824
  • 17
  • 99
  • 186
Amit Shah
  • 65
  • 12
  • 3
    What's the question? – Nathan_Sav Mar 04 '20 at 17:49
  • Not sure I understand what you need... What 'next cell for other entries' would mean? "C13" in "Sheet1" and B3 in "Sheet2"? Range "D2:D12" in "Sheet1"? Please try to better define your need in order to receive some help... – FaneDuru Mar 04 '20 at 17:52
  • Sorry for no clarity. The code is able to transpose vertical entries to B2:L2 But when i enter new data into the vertical format, the new data overrides the old data (on B2:L2). The help here is that, the data should be transposed to B3:L3 , B4:L4 B5:L5 and so on. – Amit Shah Mar 04 '20 at 17:59
  • 1
    Then you can [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and write to the next available one. – BigBen Mar 04 '20 at 18:01
  • Understood where to paste the transposed values, but would you all the time copy data from the range `Sheets("Sheet1").Range("C2:C12")`? – FaneDuru Mar 04 '20 at 18:08
  • @FaneDuru Yes Sir, copying data will always be same from same location in this scenario. – Amit Shah Mar 04 '20 at 18:11
  • It will be a little strange, on my taste... So, would you like a loop to all the time copy the same vertical range from the first sheet and transpose it on rows of the second sheet? And where to stop the loop for the second sheet range? On B12? – FaneDuru Mar 04 '20 at 18:15
  • @FaneDuru No Sir, Nothing like B12 here , that would totally depend on the data i feed into the vertical column. It is possible without a loop ? If no, how would you do it ? – Amit Shah Mar 04 '20 at 18:20
  • So, your "C12" is only an example? Would you like to copy all existing values on the vertical range? – FaneDuru Mar 04 '20 at 18:22
  • Yes, i would like to transpose all the data from C2:C12 in Sheet 1 to B2:L2 in Sheet 2. That's the idea, but thanks a lot sir for your valuable inputs, questions asked by you were helpful too. Got the answer from @BigBen. – Amit Shah Mar 04 '20 at 18:28

1 Answers1

1

If you're just trying to paste to the next available row:

With Sheets("sheet2")
    .Cells(.Rows.Count, 2).End(xlUp).offset(1).PasteSpecial Transpose:=True
End With
BigBen
  • 46,229
  • 7
  • 24
  • 40