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

 

[Mussa]

Hi peter
I'm not sure why problem shows in formatting despite of this line is existed

  Sheets("REPORT").UsedRange.EntireRow.Delete

this is what I got.

I look forward as in result in OP !
I hope to hear from you soon.

 

[Peter_SSs]

I look forward as in result in OP !
I hope to hear from you soon.

Your reply in post 9 and marking post 8 as the solution implies that the code worked for you back then. It still does for me using the post 1 sample data.
What has changed since then? (Look carefully in 'PAYABLE' in the row below the TOTAL row. Could there be something somewhere in that row?)

If your raw data has changed, please post 'RECEIVABLE' and 'PAYABLE' again with XL2BB
If you have changed the post 8 code in any way then please post the current code that you are using.

 

[Mussa]

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)

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	7	AMEER		3,000.00	-3,000.00
9	8	AMIR		300.00	-300.00
10	9	ASIIF		5,000.00	-5,000.00
11	10	AZIZ		12,000.00	########
12	11	SAMMER	1,100.00	7,000.00	-5,900.00
13	12	SANDI	3,200.00	6,000.00	-2,800.00
14	13	SANI	5,000.00	6,000.00	-1,000.00
15	14		9,300.00	39,300.00	########
16			35000	83900	-40600
REPORT

by the way after maintenance for forum in previous time XL2BB tool doesn't show colors for cells as I provide you the picture.

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

 

[Peter_SSs]

by the way after maintenance for forum in previous time XL2BB tool doesn't show colors for cells as I provide you the picture.

I don't think it is anything to do with forum maintenance. In post 1 you were using XL2BB version ..

Now you are using an older version ..

(Look carefully in 'PAYABLE' in the row below the TOTAL row. Could there be something somewhere in that row?)

Did you do that?
Run this code and report what the message box returns.

Sub Check()
  MsgBox Sheets("PAYABLE").UsedRange.Rows.Count
End Sub

 

[Mussa]

Run this code and report what the message box returns.

gives 9
problem seems from the file . I create new workbook and works .
sorry buddy for waste your time .
everything is ok.

 

[Peter_SSs]

Glad you have it sorted. Thanks for letting us know.