Monthly report requiring pre-formatted fields

[excel01noob]

Dear excel colleagues

I have a monthly report which is completed during the current month by people with some key data, basically "customer account with format A1234," purchase Id" and if "fully paid" or "instalments" in three different columns.

In the other columns data is almost the same, only exception is if customer account starts as A9... Then data in column K "sales person ID" is different (instead of 1000 it should be 2000).

We also have a column with header "date" but the data in that column should be always the last business day of the past month (file is uploaded at the beginning of the next month).

How should I create a macro that automatically fills the data in columns that don't change by replicating to the last line where customer id, purchase id and payment is?

Thank you in advance

 

[Peter_SSs]

That description is pretty hard to follow for somebody who is not familiar with your worksheet(s).
Any chance you could post a small set of dummy data and the expected results?
My signature block below has a link that has some good methods for doing that.

 

[Yongle]

the last line where customer id, purchase id and payment is

Identifying correct row
- which line is that ?
- which combination of values makes it the row to be transferred to the new worksheet ?

Column details
- what is the range of columns A to ??
- which columns contain static values ? ( ie contain values to be copied to new worksheet)

 

[excel01noob]

[TABLE="width: 1096"]
<tbody>[TR]
[TD]Appl. ID[/TD]
[TD]type[/TD]
[TD]version[/TD]
[TD]Customer account[/TD]
[TD]Purchase ID[/TD]
[TD]Payment[/TD]
[TD]Quantity[/TD]
[TD]Valid From[/TD]
[TD]Valid To[/TD]
[TD]Material[/TD]
[TD]Sales person ID[/TD]
[TD]Distrib. Party[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]5001[/TD]
[TD]02[/TD]
[TD]A70163[/TD]
[TD]NR1713632849[/TD]
[TD]fully paid[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]1133051[/TD]
[TD="align: right"]1000[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]5001[/TD]
[TD]02[/TD]
[TD]A84459[/TD]
[TD]NR1118024832[/TD]
[TD]fully paid[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]1133051[/TD]
[TD="align: right"]1000[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A15871[/TD]
[TD]NR1813144356[/TD]
[TD]instalment[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]1133051[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]5001[/TD]
[TD]02[/TD]
[TD]A96131[/TD]
[TD]NR1962598303[/TD]
[TD]fully paid[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]1133051[/TD]
[TD="align: right"]2000[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A13121[/TD]
[TD]NR1599689442[/TD]
[TD]instalment[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]28/06/2019[/TD]
[TD="align: right"]1133051[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Something like this
Column D, E, F, G is inserted by each worker.
the others are all the same and must be replicated.
I deleted the data on cell A4,B4,C4,K4,L4 which must be the same as previous ones

Column H and I it always the last business day of the month (here I used June 2019 as example).

Column K is always 1000 unless under cell D.. it starts by A9... where K... is 2000