Macro to copy and paste a range of cells based on ComboBox selection

Hi,
I have a workbook with a named range called "Client1" which has 2 columns - column 1 is the clients name and column 2 is just a number. This named range is listed in a combobox in Sheet1 and upon what client is selected I would like to copy cell range B13:H39 (Sheet1) and paste in Sheet2 at the relevant section in row7 based on the cell number to the left of clients name in sheet2 via a command button. I thought I may be able to work something out myself but I am getting nowhere. Basically I do invoicing in excel and have several customers that require their invoices to be phased and most of these invoices are just a repeat of the last invoices but with small changes. The object is to copy just the main details of an invoice to another sheet then, as required copy the invoice details back to my template without leaving Sheet1. Below is the last code I tried and I attach a workbook which may help clarify things a little. Any ideas will be most welcome.

First, delete the code you currently have in Module1. Then in Sheet1, paste the following code.

Please Login or Register  to view this content.

Assign your Recall Invoice button to the RecallPhase macro, and the Memorize button to the CopyPhase macro.

You'll also need to deal with another problem. Sheet2 has a ComboBox1_Change event behind it, which apparently gets triggered every time a value is changed anywhere in the workbook (even on another sheet). So when the CopyPhase or RecallPhase macros hit the lines that copy the data, it triggers the event, which blows up because Sheet2 isn't the current sheet. This apparently occurs because of your dynamic range (Client1) ListFillRange for your ComboBoxes. Any value change causes the dynamic range to be recalculated, which reloads the combobox, triggering the event. I learned of this from the following: http://www.mrexcel.com/forum/excel-q...worksheet.html. Perhaps you can fix it by changing OFFSET to INDEX as was suggested, or by making it a normal named range instead of dynamic. If you'd rather not, you can update the Change event to ignore the error with the following code:

Please Login or Register  to view this content.

I hope that gets you what you need.

Excellent! You may also want to consider just putting each client on a separate tab. In my opinion, it would simplify future maintenance, and you could go to each client by just selecting the tab, rather than going to sheet2 and selecting it from the combo box. It wouldn't take much code changing to accommodate.

It's certainly up to you. A separate workbook for clients is also an option.

First, delete the code you currently have in Module1. Then in Sheet1, paste the following code.

Did you follow the above step?

Definitely shouldn't have been Private. Sorry about that. I had discovered that, but apparently hadn't corrected it on code I provided.

The (Sheet1) appearance you described in step 8 is correct. I started from scratch on your attachment and everything seems to work, so there must be something else going on in your workbook. Removing Private should have taken care of missing macros.

Is your workbook especially large that might cause slow saving/opening? I've had cases where there are excessive blank rows or columns that need to be deleted. Go to each sheet and do a Ctrl-End to see where it thinks the sheet boundaries are. Otherwise, if you haven't completely closed excel or rebooted since experiencing the problems, that can often solve goofy problems that may build up when testing macros.

I don't know if it has anything to do with it, but I noticed that you used the syntax of Sheet2.Rows(6).Find. I'd never seen that before and have always used Sheets("Sheet2").Rows(6).Find, but it seemed to work either way on your sample.

madhuramji, please don't hijack someone else's post. You need to start a new one of your own.