VBA Code for Automatic Summary/Report

[Annasksd]

Hello Everyone,

Good Day !

I'm learning about excel vba coding and i have an excel file which is something like small invoices sheets and i have added one more sheet like summery/report.

Only i want to make copy and paste some specific cells from invoices to summery sheet, attached small sheet also highlighted for the reference.

sometimes invoices will be more or less , so i have to do for loop

please help anyone to get vba code

TEST INVOICE.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		INVOICE NO	ORIGIN	DESTINATION		DATE
2		2510	DXB	INDIA		01-06-2021
3	SENDER ID		2855050550
4	SHIPPER / SENDER'S NAME & ADDRESS						CONSIGNEE / RECEIVER'S NAME & ADDRESS
5	JOSE LUIS						ASHOK
6	MANGAF						THRAMMAL HOUSE
7	DXB						VADANAPPALLY
8	ID #	2855050550					CALICUT DT
9							KERALA			PINCODE	670106
10							PHONE NO:			9895995880
11	DETAILS OF SHIPEMENT				FREIGHT CHARGES (KWD)		I, the undersigned on behalf of the above sender/shipper acknowledge the receipt of the goods in good condition.
12	Total Weight (KG)		23.500		PRE PAID
13	No. of Pcs		1.00				Receiver's Name:
14	Shipment		EXPRESS COURIER				Date:			Time:
15	Description of Content		Gift				Signature:
16	INVOICE / PACKING LIST						BOX NUMBER			2510
17	SHIPPER		Same As Above				CONSIGNEE		Same As Above
18	DESCRIPTION OF GOODS
19	S.N	ITEMS	QTY	VALUE($)	S.N	ITEMS	QTY	VALUE($)	S.N	ITEMS	QTY	VALUE($)
20	1	FOOD	10	10.00	11	FOOD	10	10.00	11	FOOD	10	10.00
21	2	STATIONARY	11	5.00	12	STATIONARY	11	5.00	12	STATIONARY	11	5.00
22	3	CLOTH	6	16.00	13	CLOTH	6	16.00	13	CLOTH	6	16.00
23	4	HOUSE HOLD	3	6.00	14	HOUSE HOLD	3	6.00	14	HOUSE HOLD	3	6.00
24	5	BOOKS	2	4.00	15	BOOKS	2	4.00	15	BOOKS	2	4.00
25	6	FOOD	10	10.00	16	FOOD	10	10.00	16	FOOD	10	10.00
26	7	STATIONARY	11	5.00	17	STATIONARY	11	5.00	17	STATIONARY	11	5.00
27	8	CLOTH	6	16.00	18	CLOTH	6	16.00	18	CLOTH	6	16.00
28	9	HOUSE HOLD	3	6.00	19	HOUSE HOLD	3	6.00	19	HOUSE HOLD	3	6.00
29	10	BOOKS	2	4.00	20	BOOKS	2	4.00	20	BOOKS	2	4.00
30	NUMBER OF PIECES		1		TOTAL WEIGHT (KG)		23.500		TOTAL VALUE OF GOODS			246.00
sheet

Cell Formulas
Range	Formula
B8	B8	=C3
J16	J16	=B2
G30	G30	=C12
L30	L30	=SUM(D20:D29,H20:H29,L20:L29)

TEST INVOICE.xlsm
	A	B	C	D	E	F	G
1	SN	INOICE NO	SENDERS ADDRESS	GOODS	WEIGHT	VALUE	CONSIGNEE ADDRESS
2	1	2510	JOSE LUISMANGAF2855050550	FOOD 10 10.00 11 FOOD 10 10.00 11 FOOD 10 STATIONARY 11 5.00 12 STATIONARY 11 5.00 12 STATIONARY 11 CLOTH 6 16.00 13 CLOTH 6 16.00 13 CLOTH 6 HOUSE HOLD 3 6.00 14 HOUSE HOLD 3 6.00 14 HOUSE HOLD 3 BOOKS 2 4.00 15 BOOKS 2 4.00 15 BOOKS 2 FOOD 10 10.00 16 FOOD 10 10.00 16 FOOD 10 STATIONARY 11 5.00 17 STATIONARY 11 5.00 17 STATIONARY 11 CLOTH 6 16.00 18 CLOTH 6 16.00 18 CLOTH 6 HOUSE HOLD 3 6.00 19 HOUSE HOLD 3 6.00 19 HOUSE HOLD 3 BOOKS 2 4.00 20 BOOKS 2 4.00 20 BOOKS 2	23.500	246.00	ASHOKTHRAMMAL HOUSE VADANAPPALLYCALICUT DTKERALAPINCODE6701069895995880
3	2
4	3
5	4
Summery

Cell Formulas
Range	Formula
B2	B2	=sheet!B2
C2	C2	=sheet!A5&sheet!A6&sheet!B8
E2	E2	=sheet!C12
F2	F2	=sheet!L30
G2	G2	=sheet!G5&sheet!G6&sheet!G7&sheet!G8&sheet!G9&sheet!J9&sheet!K9&sheet!J10

 

[DanteAmor]

Try this

Sub SummaryReport()
  Dim sh As Worksheet, sumSh As Worksheet
  Dim i As Long, j As Long, lr As Long, n As Long
  Dim cad As String, c As Variant
    
  Set sumSh = Sheets("summary")
  For Each sh In Sheets
    Select Case LCase(sh.Name)
      Case LCase(sumSh.Name)
      Case Else
      
        lr = sumSh.Range("A" & Rows.Count).End(3).Row
        If lr = 1 Then n = 1 Else n = sumSh.Range("A" & lr).Value + 1
        lr = lr + 1
        
        sumSh.Range("A" & lr) = n
        sumSh.Range("B" & lr) = sh.Range("B2")
        sumSh.Range("C" & lr) = sh.Range("A5") & "|" & sh.Range("A6") & "|" & sh.Range("B8")
        
        cad = ""
        For i = 20 To 29
          For j = Columns("B").Column To Columns("L").Column
            Select Case j
              Case 2, 3, 6, 7, 10, 11
                cad = cad & sh.Cells(i, j) & "|"
            End Select
          Next j
          cad = Left(cad, Len(cad) - 1)
          cad = cad & Chr(10)
        Next i
        sumSh.Range("D" & lr) = Left(cad, Len(cad) - 1)
        
        sumSh.Range("E" & lr) = sh.Range("C12")
        sumSh.Range("F" & lr) = sh.Range("L30")
        
        cad = ""
        For Each c In Array("G5", "G6", "G7", "G8", "G9", "J9", "K9", "J10")
          cad = cad & sh.Range(c) & "|"
        Next
        sumSh.Range("G" & lr) = Left(cad, Len(cad) - 1)
        
    End Select
  Next

End Sub

 

[Annasksd]

PERFECRT, DONE

THANK YOU VERY MUCH DANTE AMORE

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.