Code for copying columns One below the other

[aravindhan_31]

Hi,

I have a data in excel like beow

Column A3:J100 product details like code, desription, suplier etc and ( row 2 is header)
from column K2 to N100 i have data for "Sales, Qty, Price,Month for Week 1 ( "1" is mentinoed in row 1 above "Sales" that is K1)

and again from M2 to P100 i have data for "Sales, Qty, Price,Month for Week 2 ( "2" is mentinoed in row 1 above "Sales" that is M1)

same thing for about 20-25 weeks in one sheet and i have 10 sheets like this 1 file

whatIi have to do manually is ,
open a new sheet,
copy from A2:100 paste Sales, Qty, Price,Month for Week 1
again copy A1-100 below paste Sales, Qty, Price,Month for Week 2
again copy A1-100 below paste Sales, Qty, Price,Month for Week 3

once we finish this go the next sheet and do the same procedure

I have attached the excelfile with input and output


Can you please help with a code for this

thakns
Arvind

[davesexcel]

Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.

[jolivanes]

Try this on a copy of your workbook

Please Login or Register  to view this content.

[aravindhan_31]

Great it works,

but it wokrs in only one sheet, i have about 10 sheets and all the data to be pasted in output sheets

can you help me to modify this

thanks for your help

[aravindhan_31]

Hi,

attached the file with actual data.

can you plese help

[jolivanes]

You said that the previous code worked so I stuck with that workbook, just added extra sheets.
See attached.

[jolivanes]

This might be what you have in mind for your last attached workbook.

[aravindhan_31]

Hi jollie,

Thanks it works at 99% as per the requirement. however when its pasting the values in Column A of output sheet that is the value from Row 9 ( in the attached J9 for example ) by default it takes the seriol number, 1,2,3,4, however that can be any number if it says 12, then i need to get 12 in col A of output sheet.

I added another sheet which started from 12, 13, 14 , for which i am getting result as 1,2,3, also can we add the sheet name in another column so that i can differenciate what sheets are used .

Thanks for your great help

[jolivanes]

Like this?

Please Login or Register  to view this content.

[aravindhan_31]

Thanks,

it runs but gives duplicates in output sheet. attached the format with 2 sheets

also i used replace all option to replace texts to blank in row 9 so that i t takes data only for weeks.

Please Login or Register  to view this content.

[jolivanes]

Where or what are the duplicates?
Do the last four Columns need to be copied into the Output Sheet also? (You replace the headers in Row 10 with blanks)

[jolivanes]

If you don't want the last four Columns, change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

I neglected to add the extra Column for the values in Row 9. Change it in the Array to something meaningful.

Please Login or Register  to view this content.

You have 1975 Rows per Sheet, 16 Columns per Sheet copied 4 Columns at once, 2 Sheets.
1975 x 16/4 x 2 = 15800. You have that amount of Rows in the Output Sheet after the macro is finished.

[aravindhan_31]

Thanks,

it worked

Thank yo so much.

Regards
ARvind