VBA making summary from several sheets with desired format

Morty

New Member
Joined
Jun 9, 2021
Messages
25
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:

Code:
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
ABCDEFGHIJKL
1
2Sheet 1Sidney01.01.2001railway bed17 01 01bricks500
317 01 02concrete200
417 03 02soil2000
517 05 04isolation20
6
7
8Sheet 2Sidney01.01.2002railway bed17 01 01bricks500
917 01 02concrete200
1017 03 02soil2000
1117 05 04isolation20
12
13
14Sheet 3Sidney01.01.2003railway bed17 01 01bricks500
1517 01 02concrete200
1617 03 02soil2000
1717 05 04isolation20
18
19
20Sheet 4Sidney01.01.2004railway bed17 01 01bricks500
2117 01 02concrete200
2217 03 02soil2000
2317 05 04isolation20
24
25
26Sheet 5Sidney01.01.2005railway bed17 01 01bricks500
2717 01 02concrete200
2817 03 02soil2000
2917 05 04isolation20
30
31
32Sheet 6Sidney01.01.2006railway bed17 01 01bricks500
3317 01 02concrete200
3417 03 02soil2000
3517 05 04isolation20
36
37
38Sheet 7Sidney01.01.2007railway bed17 01 01bricks500
3917 01 02concrete200
4017 03 02soil2000
4117 05 04isolation20
42
43
44Sheet 8Sidney01.01.2008railway bed17 01 01bricks500
4517 01 02concrete200
4617 03 02soil2000
4717 05 04isolation20
48
49
50Sheet 9Sidney01.01.2009railway bed17 01 01bricks500
5117 01 02concrete200
5217 03 02soil2000
5317 05 04isolation20
54
55
56Sheet 10Sidney01.01.2010railway bed17 01 01bricks500
5717 01 02concrete200
5817 03 02soil2000
5917 05 04isolation20
60
Summary
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E56:L59Celldoes not contain a blank value textNO
E50:L53Celldoes not contain a blank value textNO
E44:L47Celldoes not contain a blank value textNO
E38:L41Celldoes not contain a blank value textNO
E32:L35Celldoes not contain a blank value textNO
E26:L29Celldoes not contain a blank value textNO
E20:L23Celldoes not contain a blank value textNO
E14:L17Celldoes not contain a blank value textNO
E8:L11Celldoes not contain a blank value textNO
E2:L5Celldoes not contain a blank value textNO



This is the the desired formated summary:
Test.xlsm
ABC
1railway bed
2Sheet 1 01-01-011.6.-30.6.
3Waste codeWaste kindAmount [t]
417 01 01bricks500
517 01 02concrete200
617 03 02soil2000
717 05 04isolation20
8
9
10railway bed
11Sheet 2 01-01-021.6.-30.6.
12Waste codeWaste kindAmount [t]
1317 01 01bricks500
1417 01 02concrete200
1517 03 02soil2000
1617 05 04isolation20
17
18
19railway bed
20Sheet 3 01-01-031.6.-30.6.
21Waste codeWaste kindAmount [t]
2217 01 01bricks500
2317 01 02concrete200
2417 03 02soil2000
2517 05 04isolation20
26
27
28railway bed
29Sheet 4 01-01-041.6.-30.6.
30Waste codeWaste kindAmount [t]
3117 01 01bricks500
3217 01 02concrete200
3317 03 02soil2000
3417 05 04isolation20
35
36
37railway bed
38Sheet 5 01-01-051.6.-30.6.
39Waste codeWaste kindAmount [t]
4017 01 01bricks500
4117 01 02concrete200
4217 03 02soil2000
4317 05 04isolation20
44
45
46railway bed
47Sheet 6 01-01-061.6.-30.6.
48Waste codeWaste kindAmount [t]
4917 01 01bricks500
5017 01 02concrete200
5117 03 02soil2000
5217 05 04isolation20
53
54
55railway bed
56Sheet 7 01-01-071.6.-30.6.
57Waste codeWaste kindAmount [t]
5817 01 01bricks500
5917 01 02concrete200
6017 03 02soil2000
6117 05 04isolation20
62
63
64railway bed
65Sheet 8 01-01-081.6.-30.6.
66Waste codeWaste kindAmount [t]
6717 01 01bricks500
6817 01 02concrete200
6917 03 02soil2000
7017 05 04isolation20
71
72
73railway bed
74Sheet 9 01-01-091.6.-30.6.
75Waste codeWaste kindAmount [t]
7617 01 01bricks500
7717 01 02concrete200
7817 03 02soil2000
7917 05 04isolation20
80
81
82railway bed
83Sheet 10 01-01-101.6.-30.6.
84Waste codeWaste kindAmount [t]
8517 01 01bricks500
8617 01 02concrete200
8717 03 02soil2000
8817 05 04isolation20
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
ABCDEFGHIJK
1
2
3
4
5
6
7
8Waste transfer protocol
9
10Site: Sidney
11
12
13Object number:01-01-01
14
15ON name: railway bed
16
17Waste codeWaste kindAmount (t)
18
1917 01 01bricks500
2017 01 02concrete200
2117 03 02soil2000
2217 05 04isolation20
23
24
25
26
27
28
29
30
31
32For the period: 1.6.-30.6.
33
34For company:
35
36Date:
37
38
39Signature:
40
41
42
43
44
45
46
47
48
Sheet 1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C19:J30Celldoes not contain a blank value textNO



Sheet 1 01-01-01
Test.xlsm
ABCDEFGHIJK
1
2
3
4
5
6
7
8Waste transfer protocol
9
10Site: Sidney
11
12
13Object number:01-01-02
14
15ON name: railway bed
16
17Waste codeWaste kindAmount (t)
18
1917 01 01bricks500
2017 01 02concrete200
2117 03 02soil2000
2217 05 04isolation20
23
24
25
26
27
28
29
30
31
32For the period: 1.6.-30.6.
33
34For company:
35
36Date:
37
38
39Signature:
40
41
42
43
44
45
46
47
48
Sheet 2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C19:J30Celldoes not contain a blank value textNO



Sheet 1 01-01-03
Test.xlsm
ABCDEFGHIJK
1
2
3
4
5
6
7
8Waste transfer protocol
9
10Site: Sidney
11
12
13Object number:01-01-03
14
15ON name: railway bed
16
17Waste codeWaste kindAmount (t)
18
1917 01 01bricks500
2017 01 02concrete200
2117 03 02soil2000
2217 05 04isolation20
23
24
25
26
27
28
29
30
31
32For the period: 1.6.-30.6.
33
34For company:
35
36Date:
37
38
39Signature:
40
41
42
43
44
45
46
47
48
Sheet 3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C19:J30Celldoes not contain a blank value textNO
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,148,191
Messages
5,745,257
Members
423,941
Latest member
CluelessAboutExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top