title: Transposing cells in a loop using VBA in excel
body: <p>I'm trying to copy cells from one sheet(in rows) and paste them in another sheet in rows(basically transposing it). I wrote the code but could not get around the paste cell and the pastespecial command. The length of copied cells vary by each row so how do i make it select dynamically and paste the same way? As of now I'm thinking to paste a specific length and delete the blank rows at the end.  Please see the code below. It would be awesome if anybody can give me an input or idea. Thanks!!</p>
  
  <pre><code>Sub Data_Sort_Test()
  
  Dim i As Long, j As Long, k As Long, lastrow1 As Long, lastrow2 As Long
  Dim rng As Range, row As Range, rowd1 As Range, cell As Range
  Dim bidtype As String
  k = 1
  lastrow1 = Sheets("Sheet2").Range("A" &amp; Rows.Count).End(xlUp).row
  
  For i = 1 To lastrow1
  bidtype = Sheets("Sheet2").Cells(i, "A").Value 
  
  Sheets("Sheet1").Activate
  lastrow2 = Sheets("Sheet1").Range("B" &amp; Rows.Count).End(xlUp).row
  
  For j = 1 To lastrow2
  If Sheets("Sheet1").Cells(j, "B").Value = bidtype Then
  
  Sheets("Sheet2").Activate
  Sheets("Sheet2").Range(Cells(i, "B"), Cells(i, "K")).Copy
  Sheets("Sheet3").Activate
  Sheets("Sheet3").Range(Cells(j, "C"), Cells(j, "L")).Select
  ActiveSheet.Paste 'Special Transpose:=True
  'k = k + 1
  End If
  Next j
  Application.CutCopyMode = False
  Next i
  
  End Sub
  </code></pre>