Add data from one column to a new sheet and enter it on 64 vertical cells

[Butehawk]

I have a column of codes that I require to put in a new sheet and copy them 64 times before the next code is put in. Can anybody help

I have about 2100 rows in column A of the main sheet

[carsto]

you're running XL2003

2100 codes * 64 lines = 134,400 lines (plus 2 since you're starting on line 3)

since you only have 65,536 lines in XL2003 where do the remaining entries go?

[NBVC]

Try:

=INDEX($B$7:$B$9,MOD(INT((ROW()-ROW($B$7))/64),64)+1)

copied down

adjust ranges to suit.

[Butehawk]

@ carsto

Never thought of that!
I was running XL2008 on my mac but it was so slow that I fired up my PC to try and get out of a rut.
Might have to go back to the mac.
Could I put the extra onto sheets 2 and 3 by splitting the data?

Regards Roger

[JBeaucaire]

I like NBVC's formula...put here's one of my macros tweaked for your use...it creates as many sheets as needed.

Please Login or Register  to view this content.

That made 3 sheets for me from 2100 codes in about 3 seconds.

[Butehawk]

I have a list of 64 rows of text in one column, that I need to copy in block one below the other 2100 times. It comes from a sheet called "location Components Test" and has to go in the same workbook to sheets called Codes 1 to codes3. It is to be located in column B on these sheets and comes from sheet 1 starting at C3:C66.
any help much appreciated.

Roger

[JBeaucaire]

Like so:

Please Login or Register  to view this content.

You can add this line to the bottom of the first macro to call this macro automatically:

Please Login or Register  to view this content.

[Butehawk]

Great this worked you are a Master my friend.


I now have a bigger more complicated , at least in my mind it is, problem.
I have to get the relevant data from the 2100 rows of data to go where I need it on my new codes sheets.
At the moment I have hand input the data for the first one and linked it to my master data sheet " CUNN_MASTER (CODED)" .
The data that needs to be copied and pasted is in the range "D3:U66".
This data comes from row 3 on my master data sheet.
The linked data for line 3 has the referance '[CUNN_MASTER (CODED).xls}Main"$"Column Number"$3
Every time the range is pasted I have been changing the row number by "replacing" the $ "row number" by row +1.
This is very time consuming.
Can you help>

[JBeaucaire]

Can't say I follow any of that. Care to demonstrate with a clearly demonstrative sample workbook?

[Butehawk]

I attach the sheet I should of.
Sheet 1 shows what I am after from an extract of "Cunn-Master(coded)"
I have added 3 lines from this workbook to sheet 2 to let you see what I have done.
Is this any help?

[JBeaucaire]

Sorry, I have no idea. Perhaps you should close this thread and start your new question in a new thread. New threads garner the most attention from contributors.

[Butehawk]

Ok Thanks for your help so far.
I rushed what I was asking and it probably did not make sense. Hard to concentrate after a hard day at work.

[JBeaucaire]

Please stop using the QUOTE button to initiate your own replies...use the QUICK REPLY button to the right of that one. Quoting like that just makes the threads harder to read, clutters them with duplicate info for no reason. OK?

If you do have something specific, quote just that section, delete out the rest.

========

To close this thread, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[Butehawk]

Removed. Due to posting in wrong place.