Copy and paste non consecutive cells and paste consecutively in another workbook

[macquhele]

Hi guys

I have 2 excel databases. Database 1 is a data collection workbook and Database 2 is to keep consecutive records of data collected in Database 1.

Here's what happens. I have created an input sheet in Database 1 where people can enter specific data. Due to design the data is entered into cells that are not consecustive i.e. first cell is F6, followed by F8, F10, F12, F14, F16, F19, G19, H19, F22, H22, F25, H25 and F27. Most of these cells are merged cells as well.

Once data is entered they click a submit button. The macro I have recorded does the following actions:

• opens up Database 2 and selects sheet 1
• copies each piece of data one at a time from Database 1 and copies it in a row starting at row 2 on Database 2
• Once each cell is copied and pasted a new blank row is inserted on Database 2
• closes and saves database 2
• Returns to the input sheet on database 1.

However, this macro is really slow. Can anyone think of a quicker way to acheive this? All help greatly appreciated.

Regards

[JBeaucaire]

Post up your macro. Don't forget to put it in between code tags as shown in my signature.

[macquhele]

Hi JBeaucaire

As requested. I'm new to all this so hope the below is entered correctly. Thanks for a super quick response :-)

Please Login or Register  to view this content.

Regards

[JBeaucaire]

Probably would've helped to see these workbooks, too. Anyway, in this macro, edit the line that refers to "thisworkbook" to list the correct sheet name where the Submit information is coming from.

In your database, remove all the "merged" columns, they're unnecessary. The values will be placed into individual cells as shown in the macro. I also removed the formatting code. Go ahead and apply the formatting you want in the database to the columns A:K so they already have the formatting you want. The macro will add the new values to the bottom of the existing data each time.

Please Login or Register  to view this content.

[macquhele]

Hi there

Wow you are a genius. It worked a treat. Amazing. Thank you for taking the time to look at this for me.

Regards

MacQuhele

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.