Put two ranges into one range from two sheets

[Mussa]

Hello,
I want macro to combine data for two sheets into one sheet by collection ranges.

MUSA.xlsm
	A	B	C	D	E
1	ITEM	NAME	DEBIT	CREDIT	BALANCE
2	1	AMUSSAN	4,000.00		4,000.00
3	2	ASSUMAN	5,000.00	3,000.00	2,000.00
4	3	MUSSA	2,000.00	1,000.00	1,000.00
5	4	MUSSAN	3,000.00		3,000.00
6	5	MUSSI	1,300.00	300.00	1,000.00
7	6	OSMAAN	1,100.00	1,000.00	100.00
8	TOTAL		16,400.00	5,300.00	11,100.00
RECEIVABLE

Cell Formulas
Range	Formula
E2:E7	E2	=C2-D2
C8:E8	C8	=SUM(C2:C7)

MUSA.xlsm
	A	B	C	D	E
1	ITEM	NAME	DEBIT	CREDIT	BALANCE
2	1	AMEER		3,000.00	-3,000.00
3	2	AMIR		300.00	-300.00
4	3	ASIIF		5,000.00	-5,000.00
5	4	AZIZ		12,000.00	-12,000.00
6	5	SAMMER	1,100.00	7,000.00	-5,900.00
7	6	SANDI	3,200.00	6,000.00	-2,800.00
8	7	SANI	5,000.00	6,000.00	-1,000.00
9	TOTAL		9,300.00	39,300.00	-21,700.00
PAYABLE

Cell Formulas
Range	Formula
E2:E8	E2	=C2-D2
C9:D9	C9	=SUM(C2:C8)
E9	E9	=SUM(E5:E8)

result

MUSA.xlsm
	A	B	C	D	E
1	ITEM	NAME	DEBIT	CREDIT	BALANCE
2	1	AMUSSAN	4,000.00		4,000.00
3	3	ASSUMAN	5,000.00	3,000.00	2,000.00
4	4	MUSSA	2,000.00	1,000.00	1,000.00
5	5	MUSSAN	3,000.00		3,000.00
6	6	MUSSI	1,300.00	300.00	1,000.00
7	7	OSMAAN	1,100.00	1,000.00	100.00
8	8	AMEER		3,000.00	-3,000.00
9	9	AMIR		300.00	-300.00
10	10	ASIIF		5,000.00	-5,000.00
11	11	AZIZ		12,000.00	-12,000.00
12	12	SAMMER	1,100.00	7,000.00	-5,900.00
13	13	SANDI	3,200.00	6,000.00	-2,800.00
14	14	SANI	5,000.00	6,000.00	-1,000.00
15	TOTAL		25,700.00	44,600.00	-18,900.00
REPORT

thanks

 

[Peter_SSs]

I have assumed that the REPORT sheet already exists but no data is in it.
My code does nothing with the column A numbers in REPORT because I don't understand how you got those numbers in your sample results. Please explain.

Sub CombineData()
  Sheets("RECEIVABLE").UsedRange.Copy Destination:=Sheets("REPORT").Range("A1")
  Sheets("PAYABLE").UsedRange.Offset(1).Copy Destination:=Sheets("REPORT").Range("A" & Rows.Count).End(xlUp)
  With Sheets("REPORT").UsedRange
    .Cells(.Rows.Count, 3).Resize(, 3).FormulaR1C1 = "=sum(R1C:R[-1]C)"
  End With
End Sub

 

[Mussa]

My code does nothing with the column A numbers in REPORT because I don't understand how you got those numbers in your sample results. Please explain.

my apologies !
the code works greatly !
the numbers should re-autonumbering 1,2,3 . should not brings from original sheets.
and I don't need showing formulas if it's possible.

 

[Peter_SSs]

OK, try this modification.

Sub CombineData_v2()
  Sheets("RECEIVABLE").UsedRange.Copy Destination:=Sheets("REPORT").Range("A1")
  Sheets("PAYABLE").UsedRange.Offset(1).Copy Destination:=Sheets("REPORT").Range("A" & Rows.Count).End(xlUp)
  With Sheets("REPORT").UsedRange
    .Cells(.Rows.Count, 3).Resize(, 3).FormulaR1C1 = "=sum(R1C:R[-1]C)"
    .Cells(2, 1).Resize(.Rows.Count - 2).Value = Evaluate("row(1:" & .Rows.Count - 2 & ")")
    .Value = .Value
  End With
End Sub

 

[Mussa]

thanks I would delete data in REPORT sheets before brings data.
the code will repeat copying to the bottom , I don't want it .

 

[Peter_SSs]

Sorry, that is not clear to me.
Do you want the code to clear everything in REPORT before bring the data in from the other two sheets?

 

[Mussa]

Do you want the code to clear everything in REPORT before bring the data in from the other two sheets?

yes

 

[Peter_SSs]

Thanks for clarifying. Just one line to include

Sub CombineData_v3()
  Sheets("REPORT").UsedRange.EntireRow.Delete
  Sheets("RECEIVABLE").UsedRange.Copy Destination:=Sheets("REPORT").Range("A1")
  Sheets("PAYABLE").UsedRange.Offset(1).Copy Destination:=Sheets("REPORT").Range("A" & Rows.Count).End(xlUp)
  With Sheets("REPORT").UsedRange
    .Cells(.Rows.Count, 3).Resize(, 3).FormulaR1C1 = "=sum(R1C:R[-1]C)"
    .Cells(2, 1).Resize(.Rows.Count - 2).Value = Evaluate("row(1:" & .Rows.Count - 2 & ")")
    .Value = .Value
  End With
End Sub

 

[Mussa]

Thank you so much .

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.