Macro to collect data in another worksheet

Hi all,
I have developed an excel sheet for work that has its own database hidden where it collects data from to produce a price of the design a product, determined by the users requirements.

The worksheet has been distributed to three of my colleagues who all have access to the public system but use the original spreadsheet from there personal save location.

The problem I now face is I wish to have a worksheet on the public system that will be populated with the information entered into the original worksheet.

I have been picking up a lot of Excel skills lately including macro's but I am still not familiar with the VBA commands and functions and still heavily rely on the record macro function to figure out the commands.

The plan is that when the user has finished they can click the button linked to the macro to insert all the data.

So for example I would want cells B3:B7 from sheets1 copied to sheet1 of the second worksheet saved in a different file path in to the next blank row and B3:B7 on sheet2 also to the next blank row of sheet2 in the second worksheet.

If a macro code is written could you put a brief description of what each command and line does to better help me learn VBA commands and functions.

Many thanks for any help in advance,
James

I have been paying round with a few codes and have come up with this:

Please Login or Register  to view this content.

The only thing I need now is how to have the copied information pasted into the first empty cell in the D column rather than D2, how would this change the code?

Please wrap your VBA code in code tags. (see the forum rules)

I have the code working now, instead of using a search for the next blank cell I just inserted a row in row 2. All SHOULD be good now but for some reason. When new data is enter everything is shifted down except it seems cells A2 and B2.

I could paste the entire code but it is quite long and is all basically the same, taking one cell and pasting its value else where. Below is the first part of the code and the first copy.

Please Login or Register  to view this content.

Any help would be greatly appreciated as I have manged to get this far with little macro knowledge, the problem seems to stem from the fact that the cells where the data for these two problem areas are merged cells. one begin 2 cells next to each other and the other being a square of 4 cells.

Please Login or Register  to view this content.

Avoid merged cells, VBA doesn't like them.