One click copy and paste

[dw_22801]

What I have is essentially a daily product order log. I have one master table at the top of the sheet with all of the details about each product, then below, we create a new order section for each day, and just copy and paste from the master table which product we need to put on order that day. It would be nice to be able to somehow click on the row of the product which I need to add to that days orders and it is copied and pasted below in that days order section, or instruct excel somehow to move this product down to that day's orders, without having to copy and paste and scroll up and down. Any ideas?


Thanks!

[kev_]

Try this and see if it does what you want, and then we can adapt it to make it more dynamic
It depends on a named range which is called "master", currently set to column B in the cells adjacent to your table (B4:B8)
To try it out in the attached workbook. Simply "left click" on the cell in column B next to the product you want.

The code is in the sheet module. Right click on sheet TAB and View Code

Please Login or Register  to view this content.

During testing it is easy to disabled events without noticing until the macro stops working!
- run this simple macro to EnableEvents again

Please Login or Register  to view this content.

[dw_22801]

It did! What I noticed is after the 4th click, it then goes back up to the first row of that day's section, 30, and starts overwriting it.

[kev_]

Rows are now copied without overwriting - oops!

You can take out

Please Login or Register  to view this content.

unless you want to go there after pasting the row

Please Login or Register  to view this content.

Making NamedRange "master" dynamic
If you need to add rows to Master table, then use the Command Button
If you select a row in that range, it triggers the change event macro - which you do not want
By inserting rows within the "refersTo" range of any named range, the "RefersTo" range is automatically stretched - ie it becomes dynamic.

Please Login or Register  to view this content.

[dw_22801]

OMG you are saving me so much time! Question, how far over is it copying, is it just going to the end of the sheet, or does it stop at a certain column?

Thanks!

[kev_]

You are welcome

It copies the whole row.

[dw_22801]

Am I able to change it to copy only over to a certain column?

[kev_]

Does this give you what you want?

Please Login or Register  to view this content.

[dw_22801]

I think so! Question, what is the dif between a Microsoft Excel Object and a Module, in VBA? And then within the Microsoft Excel Object Folder, there is the specific sheet number and ThisWorkbook.