Excel macro of copy paste that transale both line and column into a single line

Hi all, I'm needing some help since I've tried multiple times to do it myself but was unable to achieve my goal.

Here's what I'm trying to achieve :

In my excel file, I have multiple Product sheet (ex : Product 1, Product 2). I need, when I change the date into the column F of each sheet, ask the user a question : Do you want to archive the data? If yes, we continue with the macro. If no, we bring back the date before the user change it and we stop the macro.

We need to understand that the structure of the file is that each set of data is composed of 3 lines. Colum G to AC are ungrouped and colum A to F are group so it's the same value for each line.

If user say yes to the above question, here's what I need :

I want to copy data and paste it in the Historical Data sheet using this pattern :

- First, I want to copy only the value that are different of ''-'' on the first line of my data set. (For example, in sheet Product 1, line 4, I will have 3 ungrouped column used (J-K-L)

- I want to copy column A value into column A of the Historical Sheet (always on the next available line). This value will repeat for all 3 lines created by value in column J-K-L

- I want to copy column B value into column B of the Historical Sheet. This value will repeat for all 3 lines created by value in column J-K-L

- I want to copy column C value into column C of the Historical Sheet. This value will repeat for all 3 lines created by value in column J-K-L

- I want to copy column D value into column D of the Historical Sheet. This value will repeat for all 3 lines created by value in column J-K-L

- I want to copy column F value into column E of the Historical Sheet. This value will repeat for all 3 lines created by value in column J-K-L

After that, I want :

- In Historical Data column F, we bring back the value of line 3

- In Historical Data column G, we bring back the value of line 4

- In Historical Data column H, we bring back the value of line 5

- In Historical Data column I, we bring back the value of line 6


So the trigger is the date change in column F.

We will alway bring line 3 in column F.

The data for column G-H-I in Historical Data will vary depending on which date we change.


I really hope this is clear enough for somebody to play with this.

I've joined a copy of my file.

Thanks in advance to all!

Hi, thanks so much.

Some issue with the code.

I add some info and they don't paste correctly in my Historical Data sheet

Looks like they only check for info in column J to L. If I have info different then '-' in column other J-K-L, they ignore it.

Also, if I say no the initial question, they put the date back but don't quit the macro. Can't do anything.