looking for formula/function/VBA script, to automatically copy contents from a range of cells according to the selection of a Cell, within said range

Question

Ex: In sheet 1, I have 4 columns containing customer and sales info, like: column a: names, column b:dates, column c: service, column d: amount.see image.

And in sheet 2, I have an invoice template.see image.

When selecting needed cells, ex: in row 3 that has a name, date, service and amount I want it to automatically copy and fill the invoice template on sheet 2, in the related cells. To continue, when selecting the following customer's info, ex: now in row 4, the template on sheet2 should swap details in the related cells.

I SHOULD NOT HAVE TO COPY AND PASTE, from sheeet1 that has all customer data to sheet2 for invoice template,: just select the Cells on sheet1 and automatically be copied on the invoice template on sheet2 in the specific cells to be ready to print.

I tried to Google search... with no success....

That will be a real help, also for other templates I'm using with an excel sheet.

Thank You!!:)

Answer 1

There is no formula or function that will do what you want.

However, it is a relatively simple for a macro to perform this task. There are two obvious ways to invoke the macro: 1) after selecting the four cells, press the shortcut key to invoke the macro and 2) create a the macro as a selectionchange event in the data worksheet.

The macro would process each of the selected cells and copy the contents to the appropriate cell in the invoice work sheet. If it is an event macro, there is a necessary preliminary check to confirm the selection range is exactly 4 columns wide and 1 row tall. This will prevent the macro from copying data as you try to enter or update individual cells.