VBA : copy paste rows into column

[drakko]

Dear all, i just realize that, after spending alot of time, to my horror, the database that i've been working on for weeks cannot display correctly in PIVOT table because the part of the month doesnt put as it should be. manual re-writing the database is not possible because it involved hundreds of thousands rows

after trying to do some googling and macro recording, i must admit that i cannot work this out by myself.

i would like to ask dear fellow here, what should i put in my macro, in order to do repetition of :
1. insert new rows
2. copy the rows
3. transpose the row into 1 column in another worksheet
4. move on to the next row
5. rinse and repeat until rows 100.000 something

to make it easier to understand, i have attached to screenshot.

sorry if my English is bad

regards,
Edwin

 

[RSXchin]

copy all from the first sheet.

on the next sheet, right click
paste special...
transpose

 

[drakko]

@rxschin : thanks for your reply, but unfortunately, it's not as simple as that, because from the current 100.000 rows, i must expand it into 1.2mil rows, considering that the months must be put into rows (rather than 12 columns, each column for each month)

 

[RSXchin]

well... too bad spreadsheets aren't that big...

 

[drakko]

@rxschin: thanks for the head up, i tried to delete all the old data, and it left me with 57k rows. (which mean if i spread it into 12 rows, it still only reach approx. 700k rows)

just now i googled *again* for the maximum rows per worksheet, and here's the answer

"In Excel 2007 the maximum number of rows per worksheet increased to 1,048,576 and the number of columns increased to 16,384 which is column XFD"

Read more: http://wiki.answers.com/Q/How_many_rows_and_columns_are_there_in_Excel#ixzz1KdY7w8lT
​

 

[RSXchin]

@rxschin : thanks for your reply, but unfortunately, it's not as simple as that, because from the current 100.000 rows, i must expand it into 1.2mil rows, considering that the months must be put into rows (rather than 12 columns, each column for each month)

Okay, don't worry about it. I'll see what I can write for you.

 

[RSXchin]

this assumes you have a header.

make sure you do this on a COPY of your workbook!!!!

Sub Macro1()
'
' Macro1 Macro
'
'
application.screenupdating=false
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    Rows(i).Copy
    Range(Rows(i + 1), Rows(i + 11)).Insert
    Range(Cells(i, 10), Cells(i, 20)).Copy
    Cells(i + 1, 9).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Next i
    Range("J:T").ClearContents
application.screenupdating=true
End Sub

 

[drakko]

@rxschin :

ok, i already run the macro, and still waiting for the result.. maybe will complete in couples of minutes or maybe a few hours...

my bad i didnt try it first on a few sample rows....

but fortunately, my laptop is running on i7 procie

 

[RSXchin]

haha. well, we'll see what happens when it's done.

 

[drakko]

@rxchin :

it works like charm!!! thank you!!! *hugs*

fyi, it finally completed in around 1 hour, with physical memory usage at the end around at 65%, making the laptop unable to do simple basic cut paste during the macro-time.

again, thank you for taking your time for this matter
what goes around comes around, i pray that you will also get the same blessing from others in your life.