data copying between sheets

**etacovda** · 10-14-2008, 05:51 PM

Hi guys, trying to copy some data in a spreadsheet ive made, and add some data automatically. shouldnt be too hard but not quite got my head around it.

Basically i have 2 columns of data in multiple worksheets which i wish to copy into one column on another sheet.

Ideally i want to be able to click a button and have all the data compile with the item number and serial number into sheet "compile" no matter how many sheets i have (my current macro copys the sheets with a sheet prefix onto it, ive left 31 and 32 there) automatically. Ive edited compile with one sheet of data (note i just copied 31 to 32 - normally all data would be different, not a straight copy) to show what i am trying to achieve. the 17000 will stay the same no matter what, so i just used an IF function there. Basically the part im having issues understanding is how to copy data to the next blank space in the work book, as it wont always be full of data.

At the end of getting this part working i will set it up to automatically compile into a seperate work book as a csv comma delimited to upload to a database for stocktaking purposes - but i just want to get the basic copying working first.

thanks guys

**rylo** · 10-14-2008, 08:57 PM

Hi

Try this

Please Login or Register  to view this content.

rylo

**etacovda** · 10-14-2008, 11:48 PM

you. are. the. man.

Thanks so much, im 110% sure i wouldnt have gotten that!!! thanks!!!!!!

**etacovda** · 10-15-2008, 12:19 AM

actually, i spoke too soon - im having some issues (probably because i dont fully understand what you've done rylo) - id like to be able to add data at a later date as well, and run the macro again, and have it update the compile sheet - is this possible? at present if i enter data in say, cell d11 on sheet A14, it isnt coming up in the compile list after re-running the macro - even if i delete all data. However, if i move it to D12, it will work. Thanks!

**rylo** · 10-15-2008, 12:35 AM

Hi

Interesting. Do you have data in D8:D10? It works on the principle that data will start in D8 and move down the column. It will move down until it meets the first blank cell.

Can you update your example file to represent your problem. So if you put something in D11 it doesn't work, but it does for D12.

rylo

**etacovda** · 10-15-2008, 01:44 AM

sorry, i meant B12, not D12. Im currently away from my main pc, but will upload my example tomorrow. Thanks for all your help

**etacovda** · 10-15-2008, 06:37 PM

Hi Guys (Rylo!)

Attached is a copy of what im having issues with - for some reason its doubled up a serial number etc.

**rylo** · 10-15-2008, 10:27 PM

HI

Forgot to change one of the references for the second column. Try this

Please Login or Register  to view this content.

rylo

**etacovda** · 10-17-2008, 03:00 PM

brilliant, working fine - there is for some reason some erroneous zero rows popping up, but i just made a rule that deletes them with a macro, so everything is working great

! thanks very much!!!

**rylo** · 10-17-2008, 05:42 PM

Hi

For the error rows, just check that the source data doesn't have a space instead of a blank. If that isn't it, does it appear when running against the example file that you attached?

rylo