Copy & Paste Range of Cells Based On Combo Box Value

**medihx** · 10-29-2011, 05:59 PM

Hello

In the attached workbook i have a button "Read data from Unit 1" that pulls data from a DDE server into Cells B7:AC7 and B24:AC24 which works fine.

What i would like to be able to do is have a combo box with preset values in and a button next to this so once you have selected a value the data from B7:AC7 and B24:AC24 is placed in the row that corresponds to the value in the combo box.

Also once the rows have been filled after a day of collecting data i would like another button "Archive" to copy all the data to another sheet called Archive and clear the contents of the copied cells which also works fine at the moment. However i would like the Achive button to copy the data into the next empty rows on the Archive sheet so i can build up a database over several months and if possible place a date from system time in colum A of the Achive sheet next to the copied data.

I have tried many ways already but to no success as i am a newcomer to VBA and macros.

If there is an easier way of doing all of this when anyone looks at the workbook feel free to butcher as needed.

**foxguy** · 10-29-2011, 07:09 PM

Do you know how to name a range?
It is REALLY bad to use range addresses in macros. Eventually you will want to change something, but your macros will start crashing.
If you name the range and use the name in your macros, then it won't crash when you move the data.

For Example:
At some point you want to insert rows at the top of sheet Unit 1 Daily, to make room for something. When you insert 1 row, then row 7 becomes row 8, but your macro is still looking for row 7. If the range B7:AC7 is named "DataRow1" and B24:AC24 is named "DataRow2" and your macro looks for "DataRow1" then when you insert a row, the macro still works.

Or the format of the data you're importing may change (that happened to me on a Government Site) you may only need to redefine the range and it will still keep working.

A VERY GOOD RULE: NEVER under any circumstances should you put a cell address in your macros (with the possible exception of "A1")

Here's your file back.
I improved "Copy()" to be more efficient.
I created "Copy_Time()"
I changed your box macro to "CheckCopyTo", so the user can't accidentally move the data to the wrong row.

**medihx** · 10-30-2011, 11:43 AM

Thanks Foxguy

The updated file you sent was great i just have another question.

When you press the archive button on unit1 daily and copy the data from B8:AC19 & B25:AC36 to the archive sheet the data from unit1 daily sheet B25:AC36 should goto Archive sheet AD5:BE16

At the moment it is placed under the other data and i need it to be on the righthand side of this data so the headings at the top of the page match the data what is below.

I tried to follow and edit the code but to no success it would be great if you could help me on this.

Also is it possible to grab the date from the system clock and place this in colum A on the archive sheet when the archive button is pressd if not this is not essencial i will just input it manually but i thought i would ask anyway.

Thanks again for your help and the quick responce once this is done i will be making a healthy donation to the forum as this has really helped me to save time at work and the internet is so slow here in africa trawling through lots of webpages can take all day at 5kb/sec

Regards

**foxguy** · 10-30-2011, 03:23 PM

My mistake. I assumed that since it was below on the Daily sheet that it should be below on the archive sheet. I never scrolled to the right and noticed that it over there.
Here's the update.
I added a msgbox so that if the user accidentally tries to overwrite data on the Daily sheet, it stops and asks the user if they really want to overwrite data.

I would recommend you remove the macro attached to your combo box. In my opinion it's not a good idea to have a Combo or List box execute a macro that does something that may be hard to reverse, like move data. It's too easy for someone to accidentally select the wrong time and then it's real hard to reverse because the data is erased from it's original position and it might have overwritten data from a previous move.

I personally would get rid of the combo box and button. I would set it up so that the user double clicks on the row where they want the data to go. If they double click on a row with data already there, it would put the data back in the Top Row.

**foxguy** · 10-30-2011, 04:06 PM

I forgot something in your file.
On the Daily sheet in cell AE2 you will see a #. That's where I stored the results of the Combo Box. It tells the macro what row to move the data to.

I meant to move that cell under the buttons so it would not be visible, but forgot.

If you want to move it, just "Cut and Paste" (the cell somewhere that it won't be seen (like a cell under a button). You can't just delete the cell because the Combo Box is set to put the results into $AE$2.
Or you can go into the Combo Box "Format Control" and set the "Cell Link" to a cell that's not visible (like $AE$5).

**foxguy** · 10-30-2011, 04:20 PM

Whoops

Just realized, you can't just change the "Cell Link" in the combo box. I named the cell (E2) "TimeSelected" so the macro could find it. If you just change the "Cell Link" the macro won't find it.

Either "Cut & Paste" the cell (which moves the Name and the Cell Link to where ever you Paste it.
Or Change the the "Cell Link" AND change the Name RefersTo.

Actually the "Cell Link" should be changed to "=TimeSelected" anyway. I set it up before I named the cell.