VBA making summary from several sheets with desired format

[Morty]

Hello everyone,

some time ago I created a code here with great help from DanteAmor which can extract the selected data from all sheets and create a summary from them (see "Summary" list). I would like to thank him once again for that.
But since I will use this code for a few more years and I will also present it to my colleagues and superiors. I would like to ask if the formatting could be modified directly in the code so that the output looks like in the "Desired result" sheet. Unfortunately, the original sheets with key data must remain like this, including the merged cells for formatting and printing. But the way to create that formated summary is entirely up to the author :D.

Alternatively, please highlight the format editing section in your code and I would try to play with it myself later :D.
it would be easier to put the whole excel sheet here, but it cannot be so I uploaded individual sheets here by using XL2BB.

Thanks again to everyone involved and have a nice rest of the day.

With regards
Morty

the original code is:

Sub Souhrn()

  Dim sh As Worksheet, sumSh As Worksheet
  Dim i As Long, lr1 As Long, lr2 As Long, n As Long
  
  Application.ScreenUpdating = False
 
  Set sumSh = Sheets("Souhrn")
  sumSh.Range("A2:L" & Rows.Count).Clear
 
  lr1 = 2
  For Each sh In Sheets
    Select Case LCase(sh.Name)
      Case LCase(sumSh.Name), LCase("Souhrn")
      Case Else
        lr2 = 19
        Do While sh.Range("C" & lr2).Value <> ""
          lr2 = lr2 + 1
        Loop
        lr2 = lr2 - 1
        If lr2 < 19 Then lr2 = 19
      
        sh.Range("C19:J" & lr2).Copy
        sumSh.Range("D" & lr1).PasteSpecial xlPasteValues
        sumSh.Range("D" & lr1).PasteSpecial xlPasteFormats
        n = lr2 - 18
    
        Call Format_Cells(n, sumSh.Range("A" & lr1), sh.Name)
        Call Format_Cells(n, sumSh.Range("B" & lr1), sh.Range("E10").Value)
        Call Format_Cells(n, sumSh.Range("C" & lr1), sh.Range("E13").Value)
        Call Format_Cells(n, sumSh.Range("D" & lr1), sh.Range("E15").Value)

        lr1 = lr1 + n + 2
    End Select
  Next
End Sub

Sub Format_Cells(n As Long, xRange As Range, xValue As Variant)
  With xRange
    .Resize(n).Merge
    .Resize(n).Borders.LineStyle = xlContinuous
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Value = xValue
  End With
End Sub

This is the original summary constructed by original code above (the data in C colums should reflect site code designation: 01-01-01; 01-01-02 etc. but it puts it in in date format sadly):

Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Sheet 1	Sidney	01.01.2001	railway bed	17 01 01			bricks			500
3					17 01 02			concrete			200
4					17 03 02			soil			2000
5					17 05 04			isolation			20
6
7
8	Sheet 2	Sidney	01.01.2002	railway bed	17 01 01			bricks			500
9					17 01 02			concrete			200
10					17 03 02			soil			2000
11					17 05 04			isolation			20
12
13
14	Sheet 3	Sidney	01.01.2003	railway bed	17 01 01			bricks			500
15					17 01 02			concrete			200
16					17 03 02			soil			2000
17					17 05 04			isolation			20
18
19
20	Sheet 4	Sidney	01.01.2004	railway bed	17 01 01			bricks			500
21					17 01 02			concrete			200
22					17 03 02			soil			2000
23					17 05 04			isolation			20
24
25
26	Sheet 5	Sidney	01.01.2005	railway bed	17 01 01			bricks			500
27					17 01 02			concrete			200
28					17 03 02			soil			2000
29					17 05 04			isolation			20
30
31
32	Sheet 6	Sidney	01.01.2006	railway bed	17 01 01			bricks			500
33					17 01 02			concrete			200
34					17 03 02			soil			2000
35					17 05 04			isolation			20
36
37
38	Sheet 7	Sidney	01.01.2007	railway bed	17 01 01			bricks			500
39					17 01 02			concrete			200
40					17 03 02			soil			2000
41					17 05 04			isolation			20
42
43
44	Sheet 8	Sidney	01.01.2008	railway bed	17 01 01			bricks			500
45					17 01 02			concrete			200
46					17 03 02			soil			2000
47					17 05 04			isolation			20
48
49
50	Sheet 9	Sidney	01.01.2009	railway bed	17 01 01			bricks			500
51					17 01 02			concrete			200
52					17 03 02			soil			2000
53					17 05 04			isolation			20
54
55
56	Sheet 10	Sidney	01.01.2010	railway bed	17 01 01			bricks			500
57					17 01 02			concrete			200
58					17 03 02			soil			2000
59					17 05 04			isolation			20
60
Summary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E56:L59	Cell	does not contain a blank value	text	NO
E50:L53	Cell	does not contain a blank value	text	NO
E44:L47	Cell	does not contain a blank value	text	NO
E38:L41	Cell	does not contain a blank value	text	NO
E32:L35	Cell	does not contain a blank value	text	NO
E26:L29	Cell	does not contain a blank value	text	NO
E20:L23	Cell	does not contain a blank value	text	NO
E14:L17	Cell	does not contain a blank value	text	NO
E8:L11	Cell	does not contain a blank value	text	NO
E2:L5	Cell	does not contain a blank value	text	NO

This is the the desired formated summary:

Test.xlsm
	A	B	C
1	railway bed
2	Sheet 1	01-01-01	1.6.-30.6.
3	Waste code	Waste kind	Amount [t]
4	17 01 01	bricks	500
5	17 01 02	concrete	200
6	17 03 02	soil	2000
7	17 05 04	isolation	20
8
9
10	railway bed
11	Sheet 2	01-01-02	1.6.-30.6.
12	Waste code	Waste kind	Amount [t]
13	17 01 01	bricks	500
14	17 01 02	concrete	200
15	17 03 02	soil	2000
16	17 05 04	isolation	20
17
18
19	railway bed
20	Sheet 3	01-01-03	1.6.-30.6.
21	Waste code	Waste kind	Amount [t]
22	17 01 01	bricks	500
23	17 01 02	concrete	200
24	17 03 02	soil	2000
25	17 05 04	isolation	20
26
27
28	railway bed
29	Sheet 4	01-01-04	1.6.-30.6.
30	Waste code	Waste kind	Amount [t]
31	17 01 01	bricks	500
32	17 01 02	concrete	200
33	17 03 02	soil	2000
34	17 05 04	isolation	20
35
36
37	railway bed
38	Sheet 5	01-01-05	1.6.-30.6.
39	Waste code	Waste kind	Amount [t]
40	17 01 01	bricks	500
41	17 01 02	concrete	200
42	17 03 02	soil	2000
43	17 05 04	isolation	20
44
45
46	railway bed
47	Sheet 6	01-01-06	1.6.-30.6.
48	Waste code	Waste kind	Amount [t]
49	17 01 01	bricks	500
50	17 01 02	concrete	200
51	17 03 02	soil	2000
52	17 05 04	isolation	20
53
54
55	railway bed
56	Sheet 7	01-01-07	1.6.-30.6.
57	Waste code	Waste kind	Amount [t]
58	17 01 01	bricks	500
59	17 01 02	concrete	200
60	17 03 02	soil	2000
61	17 05 04	isolation	20
62
63
64	railway bed
65	Sheet 8	01-01-08	1.6.-30.6.
66	Waste code	Waste kind	Amount [t]
67	17 01 01	bricks	500
68	17 01 02	concrete	200
69	17 03 02	soil	2000
70	17 05 04	isolation	20
71
72
73	railway bed
74	Sheet 9	01-01-09	1.6.-30.6.
75	Waste code	Waste kind	Amount [t]
76	17 01 01	bricks	500
77	17 01 02	concrete	200
78	17 03 02	soil	2000
79	17 05 04	isolation	20
80
81
82	railway bed
83	Sheet 10	01-01-10	1.6.-30.6.
84	Waste code	Waste kind	Amount [t]
85	17 01 01	bricks	500
86	17 01 02	concrete	200
87	17 03 02	soil	2000
88	17 05 04	isolation	20
Desired result

And these are 3 example sheets from all of 10 (there could be even more):
Sheet 1 01-01-01 (here i highlighted key cells wtih data which I use for summary)

Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6
7
8				Waste transfer protocol
9
10			Site:		Sidney
11
12
13			Object number:		01-01-01
14
15			ON name:		railway bed
16
17			Waste code			Waste kind			Amount (t)
18
19			17 01 01			bricks			500
20			17 01 02			concrete			200
21			17 03 02			soil			2000
22			17 05 04			isolation			20
23
24
25
26
27
28
29
30
31
32			For the period:		1.6.-30.6.
33
34			For company:
35
36			Date:
37
38
39			Signature:
40
41
42
43
44
45
46
47
48
Sheet 1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C19:J30	Cell	does not contain a blank value	text	NO

Sheet 1 01-01-01

Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6
7
8				Waste transfer protocol
9
10			Site:		Sidney
11
12
13			Object number:		01-01-02
14
15			ON name:		railway bed
16
17			Waste code			Waste kind			Amount (t)
18
19			17 01 01			bricks			500
20			17 01 02			concrete			200
21			17 03 02			soil			2000
22			17 05 04			isolation			20
23
24
25
26
27
28
29
30
31
32			For the period:		1.6.-30.6.
33
34			For company:
35
36			Date:
37
38
39			Signature:
40
41
42
43
44
45
46
47
48
Sheet 2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C19:J30	Cell	does not contain a blank value	text	NO

Sheet 1 01-01-03

Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6
7
8				Waste transfer protocol
9
10			Site:		Sidney
11
12
13			Object number:		01-01-03
14
15			ON name:		railway bed
16
17			Waste code			Waste kind			Amount (t)
18
19			17 01 01			bricks			500
20			17 01 02			concrete			200
21			17 03 02			soil			2000
22			17 05 04			isolation			20
23
24
25
26
27
28
29
30
31
32			For the period:		1.6.-30.6.
33
34			For company:
35
36			Date:
37
38
39			Signature:
40
41
42
43
44
45
46
47
48
Sheet 3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C19:J30	Cell	does not contain a blank value	text	NO