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]

Hi and welcome to MrExcel.

i want to make copy and paste some specific cells from invoices to summery sheet

You mean summary sheet.

Check if this works for you. I did some enhancement to concatenate the results. If they don't work for you, I can delete them.

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
            cad = cad & sh.Cells(i, j) & "|"
          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]

Hi and welcome to MrExcel.


You mean summary sheet.

Check if this works for you. I did some enhancement to concatenate the results. If they don't work for you, I can delete them.

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
            cad = cad & sh.Cells(i, j) & "|"
          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

Dear DanteAm,

thanks for the quick reply and code, i have corrected as 'summary", unfortunately its not working with me.

when i run the code not getting any error message, but data not reflecting to the summary sheet

​

 

[DanteAmor]

Do you have a "sheet" in the same book where you have the "summary" sheet?

 

[Annasksd]

Do you have a "sheet" in the same book where you have the "summary" sheet?

Dear DanteAmor,

Yes, these are all sheets in one workbook, four invoice sheet & one summary sheet

 

[Annasksd]

Do you have a "sheet" in the same book where you have the "summary" sheet?

Dear DanteAmor,

Yes, these are all sheets in one workbook, four invoice sheet & one summary sheet

i can write exact cell range;
Copy from Invoice sheet and paste to summary sheet

B2 to B2
A5:B8 to C2
G5:K10 to G2
C12 to E2
B20:C29,F20:G29,J20:K29 to D2
L30 to F2

thank you so much

 

[DanteAmor]

So does the macro already work for you?

 

[Annasksd]

So does the macro already work for you?

Not working that code

 

[DanteAmor]

Test with my test file. Just press the run macro button on the summary sheet

summary macro.xlsm

drive.google.com

 

[Annasksd]

Dear DanteAmor,

Perfectly working your file, THANK YOU SO MUCH FOR YOUR TIME AND HELP.

I have added one more sheet as sometimes invoices more or less, now perfectly showing all sheets details on the summary sheet.

Just only one thing, two more columns extra copied from invoice sheet which is not required (Value & S.N) eg:- FOOD|3|10|11||||21|||

Appreciated, thanks & Regards,