Copying multiple rows of data from a sheet to single rows on a new sheet

[Pocko]

Hello all,

I have a need to fill out forms and when i'm done i'd like to click on a button and archive the data to a "master" sheet with all of the data recorded on single row.

What i need is some code to copy the cells of my form data one row at a time over to the next empty single row of my master sheet. I do have blank cells, and i also have rows that end in blank cells which i have to retain. I also wish to retain the original formatting which contain dynamic range drop down lists.

My data range is in columns A to BO, and i have 85 rows.

When i run it again it will repeat the process on the next empty row, etc, etc...

So in short my data on my origin should be as follows -

A B C
1 APPLE ORANGE (EMPTY)
2 GRAPE LEMON PEAR

And i want it on the new sheet as -

A B C D E F
1 APPLE ORANGE (EMPTY) GRAPE LEMON PEAR

If i trigger it again i get -

A B C D E F
1 APPLE ORANGE (EMPTY) GRAPE LEMON PEAR
2 APPLE ORANGE (EMPTY) GRAPE LEMON PEAR

Any help would be awesome...Thanks

 

[SydneyGeek]

If I understand correctly you have 67 columns X 85 rows, and would like to transpose that to 5695 columns X 1 row. Which version of Office are you using? It's possible to do in 2007 or 2010 but I'm not sure that I would be comfortable using that many columns.
Would you be able to work with a setup that just copied the form layout as is, ie 67 X 85? If you want it all on one row to be able to (say) push the data to a database, I don't know any database systems that support that many columns.

Denis

 

[Pocko]

Hi Denis,

Thanks for taking the time to offer your thoughts. You are right 67 columns it is. I am using Office 2010.

In short i need nowhere near 67 columns width for the actual data entry. i didn't know about any limitations when i designed the forms! So i may be able to trim it down. It is a fairly detailed form and the 67 columns is just what i needed to get my information displayed corrrectly at the time of design.

As long as i can get some code to do what i need to do, i'll be happy to trim down the number of columns to suit the limitations of excel.

I really do need the data to end up in rows though. We do thousands of these inspection each year and a client wants it in rows.

Reagrds - Pocko.

 

[Pocko]

Sorry Denis i forgot to mention that i dont want to transpose the columns to rows (if understand the term transpose correctly).

I just want to copy the range of cells from A1:BO1 over to the master sheet and paste them into A1:BO1, then go back to the form and copy A2:BO2 over to the master sheet starting pasting it at BP1:ED1 and then repeat or loop through the process for the other 83 rows.

Cheers...

 

[SydneyGeek]

You may find that using range names to define the data entry cells will cut things down for you.
Let's say there are 200 actual data entry items on the form, not all of which will be entered every time. That is only 200 columns of data required to store the information.
It's fairly straightforward to harvest that sort of data to single rows, and then return it when required so that you (or the client) can see the original layout.

Denis