Help with looping a macro

**jumpingoff** · 11-27-2012, 03:23 PM

Hi,

New to all of this (as in first day!) and I've managed to cobble a couple of macros together to format a worksheet but I need help with this one in particular.

What I have so far is what I've recorded myself. I'm trying to copy a selection of cells from a row and transpose them into a (pre-existing) column. What I need to have happen is for this macro to repeat down the page until there are no more rows to transpose.

Each row has unique data so I am not looking to repeat a single row down the page. Each row I am transposing exists from columns E:Q. Each row will transpose into column D. Also, each row with data that I am transposing has 12 blank rows between it (in order to fit the row turned column).

So it should select, transpose, delete, skip 12 rows, select, transpose, delete, skip 12 rows, etc. Here is the code I have so far and a before and after screen shot. Thanks for any help.

Sub transpose_qty()
' transpose_qty Macro
Range("E2:Q2").Select
Selection.Copy
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E2:Q2").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

**HaHoBe** · 11-27-2012, 04:06 PM

Hi, jumpingoff,

maybe start opff with somehting like this:

Sub Transpose_re()
Dim lngLastRow As Long
Dim lngCounter As Long

Const clngNO_Col As Long = 13

lngLastRow = Cells(Rows.Count, "E").End(xlUp).Row
For lngCounter = 2 To lngLastRow Step clngNO_Col
  Range("D" & Cells(Rows.Count, "D").End(xlUp).Row + 1).Resize(clngNO_Col, 1).Value = _
      WorksheetFunction.transpose(Cells(lngCounter, "E").Resize(1, clngNO_Col))
Next lngCounter
End Sub

Ciao,
Holger