Copy last row for each range contains names under headers

[abdo meghari]

Hello
I would macro to deal with 300 names , and . every name contains range will be different ho many rows contain for each range , so the the total rows in sheet will be 11000 as maximum.
the result should be in REPORT sheet based on NAMES sheet
REPORT sheet in column B will brings last date before SUM row in NAMES sheet , column C will bring names from column D under NAMES header, as to columns D:F will brings from SUM row in NAMES sheet are existed in columns E:G .
in REPORT sheet contains SUM row if there is no empty row then should insert new row with the same formatting and borders and expand range in formulas in SUM row

ERROR MERGING.xlsm
	A	B	C	D	E	F	G
1				NAMES
2				CCF-1000
3	ITEM	DATE	INV.NO	CASE	DEBIT	CREDIT	BALANCE
4	1	30/06/2023			-	-500.00	-500.00
5	2	15/07/2023		RVCH20000	30,000.00	-	29,500.00
6	3	15/07/2023		RVCH20002	10,000.00	-	39,500.00
7	4	15/09/2023	BSTR_23448	OUTSANDING	-	2,300.00	37,200.00
8	5	15/09/2023	BSTR_23449	OUTSANDING	-	1,920.00	35,280.00
9	6	15/09/2023	BSTR_23450	PAID	50,400.00	-	85,680.00
10	7	15/09/2023	BSJ_23444	OUTSANDING	1,720.00	-	87,400.00
11	8	15/09/2023	BSJ_23446	PAID	-	4,900.00	82,500.00
12	9	15/09/2023	VSTR_23444	PAID	-	500.00	82,000.00
13	10	15/09/2023	VSTR_23446	OUTSANDING	3,600.00	-	85,600.00
14	11	15/09/2023	RSS_23222	OUTSANDING	-	860.00	84,740.00
15	12	15/09/2023		VCH20005	-	15,000.00	69,740.00
16	13	16/09/2023	VSTR_23449	PAID	-	3,760.00	65,980.00
17	14	16/09/2023	RSS_23224	PAID	2,950.00	-	68,930.00
18	SUM				98,670.00	28,740.00	69,930.00
19
20
21				NAMES
22				CCF-1001
23	ITEM	DATE	INV.NO	CASE	DEBIT	CREDIT	BALANCE
24	1	30/06/2023			2,000.00	-	2,000.00
25	2	15/07/2023		RVCH20001	25,000.00	-	27,000.00
26	3	15/09/2023	VSTR_23445	OUTSANDING	1,000.00	-	28,000.00
27	4	16/09/2023	BSTR_23452	PAID	7,200.00	-	35,200.00
28	SUM				35,200.00	-	35,200.00
NAMES

ERROR MERGING.xlsm
	A	B	C	D	E	F
1	ITEM	DATE	NAMES	DEBIT	CREDIT	BALANCE
2
3	SUM			0.00	0.00	0.00
REPORT

Cell Formulas
Range	Formula
D3:E3	D3	=SUM(D2:D2)
F3	F3	=D3-E3

result

ERROR MERGING.xlsm
	A	B	C	D	E	F
1	ITEM	DATE	NAMES	DEBIT	CREDIT	BALANCE
2	1	16/09/2023	CCF-1000	98,670.00	28,740.00	69,930.00
3	2	16/09/2023	CCF-1001	35,200.00	-	35,200.00
4	SUM			133,870.00	28,740.00	105,130.00
REPORT

Cell Formulas
Range	Formula
D4:E4	D4	=SUM(D2:D3)
F4	F4	=D4-E4

 

[DanteAmor]

Try:

Sub CreateReport()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, lr As Long
  Dim totD As Double, totC As Double, totB As Double
  
  a = Sheets("NAMES").Range("A1", Sheets("NAMES").Range("G" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a), 1 To 6)
  
  For i = 1 To UBound(a)
    If a(i, 4) = "NAMES" Then
      k = k + 1
      b(k, 1) = k
      b(k, 3) = a(i + 1, 4)
    End If
    If a(i, 1) = "SUM" Then
      b(k, 2) = a(i - 1, 2)
      b(k, 4) = Val(a(i, 5))
      b(k, 5) = Val(a(i, 6))
      b(k, 6) = Val(a(i, 5)) - Val(a(i, 6))
      totD = totD + b(k, 4)
      totC = totC + b(k, 5)
      totB = totB + b(k, 6)
    End If
  Next
  
  With Sheets("REPORT")
    .Range("A2:F" & Rows.Count).Clear
    .Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
    
    'FORMTA CELLS
    lr = .Range("A" & Rows.Count).End(3).Row + 1
    .Range("A" & lr).Value = "SUM"
    .Range("D" & lr).Value = totD
    .Range("E" & lr).Value = totC
    .Range("F" & lr).Value = totB
    .Range("A:F").HorizontalAlignment = xlCenter
    .Range("B:B").NumberFormat = "dd/mm/yyyy"
    .Range("D:F").NumberFormat = "#,##0.00;-#,##0.00;-"
    .Range("A2:F" & lr).Borders.LineStyle = xlContinuous
    .Range("A1").Copy
    .Range("A" & lr).PasteSpecial xlPasteFormats
  End With
  Application.CutCopyMode = False
End Sub

 

[abdo meghari]

Hi Dant again,
the code is great !

I would fix color for Sum word in last row , when change SUM word in different location will leave color in previous location . should delete formatting from previous cell in column A was highlighed and bold font

 

[abdo meghari]

sorry I know my bad !

just curiosity how can I repeat copy to the bottom for the same name instead of replace, please?
thank you

 

[DanteAmor]

just curiosity how can I repeat copy to the bottom for the same name instead of replace, please?

I don't understand, you didn't put it in your examples nor did you mention it in your OP.

I'll try to guess

Sub CreateReport()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, lr As Long
  Dim totD As Double, totC As Double, totB As Double
  
  a = Sheets("NAMES").Range("A1", Sheets("NAMES").Range("G" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a), 1 To 6)
  
  For i = 1 To UBound(a)
    If a(i, 4) = "NAMES" Then
      k = k + 1
      b(k, 1) = k
      b(k, 3) = a(i + 1, 4)
    End If
    If a(i, 1) = "SUM" Then
      b(k, 2) = a(i - 1, 2)
      b(k, 4) = Val(a(i, 5))
      b(k, 5) = Val(a(i, 6))
      b(k, 6) = Val(a(i, 5)) - Val(a(i, 6))
      totD = totD + b(k, 4)
      totC = totC + b(k, 5)
      totB = totB + b(k, 6)
    End If
  Next
  
  With Sheets("REPORT")
    '.Range("A2:F" & Rows.Count).Clear
    lr = .Range("A" & Rows.Count).End(3).Row + 1
    .Range("A" & lr).Resize(UBound(b, 1), UBound(b, 2)).Value = b
    
    'FORMTA CELLS
    lr = .Range("A" & Rows.Count).End(3).Row + 1
    .Range("A" & lr).Value = "SUM"
    .Range("D" & lr).Value = totD
    .Range("E" & lr).Value = totC
    .Range("F" & lr).Value = totB
    .Range("A:F").HorizontalAlignment = xlCenter
    .Range("B:B").NumberFormat = "dd/mm/yyyy"
    .Range("D:F").NumberFormat = "#,##0.00;-#,##0.00;-"
    .Range("A2:F" & lr).Borders.LineStyle = xlContinuous
    .Range("A1").Copy
    .Range("A" & lr).PasteSpecial xlPasteFormats
  End With
  Application.CutCopyMode = False
End Sub

If it is not what you need, you will have to be more specific with examples, but from the first post.

 

[abdo meghari]

when I run the code every time repeat copy to the bottom and put duplicate name under each other of them like this

Lastrow
	A	B	C	D	E	F
1	ITEM	DATE	NAMES	DEBIT	CREDIT	BALANCE
2	1	16/09/2023	CCF-1000	98,670.00	28,740.00	69,930.00
3	2	16/09/2023	CCF-1000	98,670.00	28,740.00	69,930.00
4	3	16/09/2023	CCF-1000	98,670.00	28,740.00	69,930.00
5	4	16/09/2023	CCF-1000	98,670.00	28,740.00	69,930.00
6	5	16/09/2023	CCF-1001	35,200.00	-	35,200.00
7	6	16/09/2023	CCF-1001	35,200.00	-	35,200.00
8	7	16/09/2023	CCF-1001	35,200.00	-	35,200.00
9	8	16/09/2023	CCF-1001	35,200.00	-	35,200.00
10	SUM			535,480.00	114,960.00	420,520.00
REPORT

 

[DanteAmor]

I still don't understand what you need.
As I said in the previous post, it wasn't in your OP.

 

[abdo meghari]

ok Dante , just I said

curiosity

don't worry everything is ok
thanks again for every thing.