Thread: Copy/Paste Automation Question

Hi All,

I am doing something that should be easily automated, but I just can't think of how to do it without having to type a ton of ranges into vba (which of course makes me realize there has to be a way to loop through the data, I just don't know how).

The top of the image below shows what the data looks like when I receive it, and the bottom half is the way I need the data to look. Manually copy/paste transpose can be a pain when there are hundreds of rows...

sample Data.JPG

I have attached a file with the sample data.

I am using Excel 2007.

Thanks in advance!

Are the items always grouped in 3s or can it vary?

It can vary. There will be probably 100 dates at the top, maybe five categories to the left. I figured if someone was able to just give me the code for this I could tweak it depending on how many items.

This worked for your example. It deletes the original data. It uses the position of the Amount column to work everything out and assumes the data are grouped by column A:

Sub x()

Dim vOut(), vInput(), i As Long, j As Long, k As Long, c As Long, n As Long

c = Rows(1).Find("Amount").Column
n = WorksheetFunction.CountIf(Columns(1), Range("A2"))

With Range("A1").CurrentRegion
    vInput = .Value
    .Offset(1).Clear
End With

ReDim vOut(1 To UBound(vInput, 1), 1 To (c - 2) + n)
ReDim nCounts(1 To UBound(vInput, 1))

For i = 2 To UBound(vInput, 1) Step n
    k = k + 1
    For j = 1 To c - 2
        vOut(k, j) = vInput(i, j)
    Next j
    For j = c - 1 To c - 2 + n
        vOut(k, j) = vInput(i + (j - c + 1), c)
    Next j
Next i

With Range("A1").Offset(, c - 2)
    For j = 1 To n
        .Offset(, j - 1) = vInput(j + 1, c - 1)
    Next j
    .Resize(, n).NumberFormat = "mmm-yy"
End With

Range("A2").Resize(k, c - 2 + n) = vOut

End Sub

Thanks I appreciate it! This will save me tons of time!