Balance Sheet - Bring In Numbers From Other Tab/Sheet

[richmcgill]

I have to balance loan accounts daily and what would be the best way to do this?

I have worksheet #1 which has a credit and debit side.
I have a report I place on a second tab / Worksheet and add amounts posted to each loan and look for differences in amounts.
Worksheet 2 I need to automatically move Phil and Tony negative amounts with the Loan Number, Customer Name and Negative Amounts changed to a positive number on the Debit side of Worksheet 1.
Worksheet 2 I need t automatically move Joe & George positive amounts with the Loan Number, Customer Name and Positive Amounts and leaving them as positive numbers to the Credit Side of Worksheet 1 .

There are many other loans on Workshee1 and I need this information to stay pure. A Macro or What Formula Best moves the information quickly and accurately?

Worksheet 1
Credit	Debit
Date	Loan Number	Customer Name	Amount	Date	Loan Number	Customer Name	Amount
5/19/2019	383894	Tom	$ 739.99	5/19/2019	28748	Kyle	$47.99
5/19/2019	3456	Dale	$ 349.98	5/19/2019	133656	Skip	$7,834.00
5/19/2019	6789	George	$ 392.55	5/19/2019	28497	Phil	$699.99
5/19/2019	87263	Joe	$ 329.21	5/19/2019	87263	Tony	$884.98
Worksheet 2
Date	Loan Number	Customer Name	Amount 1	Amount 2	Total	Posted	Difference
5/19/2019	64203	Abe	$199.99	$333.99	$533.98	$533.98	$0.00
5/19/2019	87263	Joe	$2,188.23	$626.43	$2,814.66	$3,143.87	$329.21
5/19/2019	28497	Phil	$3,199.87	$645.99	$3,845.86	$3,145.87	-$699.99
5/19/2019	64203	John	$2,145.32	$9,834.09	$11,979.41	$11,979.41	$0.00
5/19/2019	87263	Tony	$626.43	$884.98	$1,511.41	$626.43	-$884.98
5/19/2019	28497	Al	$645.99	$645.99	$1,291.98	$1,291.98	$0.00
5/19/2019	6789	George	$333.43	$0.00	$333.43	$725.98	$392.55

<colgroup><col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2446;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3669;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4010;" span="3"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4010;"> <tbody> </tbody>

 

[DanteAmor]

Try this
Assuming that the data in both sheets start in cell A1

Sub Balance_Sheet()
    Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, lr1 As Long, lr3 As Long
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    sh1.Activate
    If sh2.AutoFilterMode Then sh2.AutoFilterMode = False
    sh2.Range("A1").AutoFilter 8, ">0"
    lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
    If lr > 1 Then
        sh2.Range("A2:C" & lr & ",H2:H" & lr).Copy
        sh1.Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
    End If
    
    If sh2.AutoFilterMode Then sh2.AutoFilterMode = False
    sh2.Range("A1").AutoFilter 8, "<0"
    lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
    If lr > 1 Then
        sh2.Range("A2:C" & lr & ",H2:H" & lr).Copy
        lr1 = sh1.Range("E" & Rows.Count).End(xlUp).Row
        sh1.Range("E" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
        lr3 = sh1.Range("E" & Rows.Count).End(xlUp).Row
        With sh1.Range("H" & lr1 + 1 & ":H" & lr3)
            .Value = Evaluate(.Address & "*-1")
        End With
    End If
    If sh2.AutoFilterMode Then sh2.AutoFilterMode = False
    Application.CutCopyMode = False
End Sub

 

[richmcgill]

Thank you very much for the help.

I really do appreciate everyone taking the time to help.

 

[richmcgill]

What would a Macro look like for this? This entire process has about twelve steps and there will be a lot of different users and I need to make it as full proof as I can.
This can be added to the macro you have already built and combine into one step? The Loan Number Column is B6. This is simply a general subtract problem which leads to the macro you provided earlier. If you have time to look at this it would be appreciated. I can post the question separately if needed or if you do not have time to take a look.

Either way thank you for the help you have already provided.

														Positive number goes in shortage/debit side
Loan #		Name		Interest		Escrows		Interest & Escrow
333				173.47		$882.61		$0.00		Sum F6 & H6 Formula		Manual Entry		Formula N6-L6
334				327.69		$1,555.54		$0.00		$1,883.23		$2,121.55		$238.32
335				177.48		$390.46		$0.00		$567.94		$390.46		($177.48)
336				716.04		$1,294.01		$0.00		$2,010.05		$2,010.05		$0.00
337				436.92		$1,665.97		$0.00		$2,102.89		$2,247.22		$144.33
338				151.2		$852.20		$0.00		$1,003.40		$852.20		($151.20)
339				150.26				$0.00		$150.26		$144.10		($6.16)
340				460.32		$417.76		$0.00		$878.08		$417.76		($460.32)
341				451.08		$1,013.79		$0.00		$1,464.87		$1,464.87		$0.00
342										$5,000.00		$1,464.87		($3,535.13)

<tbody>
</tbody>

 

[DanteAmor]

Thank you very much for the help.

I really do appreciate everyone taking the time to help.

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

 

[DanteAmor]

What would a Macro look like for this? This entire process has about twelve steps and there will be a lot of different users and I need to make it as full proof as I can.
This can be added to the macro you have already built and combine into one step? The Loan Number Column is B6. This is simply a general subtract problem which leads to the macro you provided earlier. If you have time to look at this it would be appreciated. I can post the question separately if needed or if you do not have time to take a look.

Either way thank you for the help you have already provided.

														Positive number goes in shortage/debit side
Loan #		Name		Interest		Escrows		Interest & Escrow
333				173.47		$882.61		$0.00		Sum F6 & H6 Formula		Manual Entry		Formula N6-L6
334				327.69		$1,555.54		$0.00		$1,883.23		$2,121.55		$238.32
335				177.48		$390.46		$0.00		$567.94		$390.46		($177.48)
336				716.04		$1,294.01		$0.00		$2,010.05		$2,010.05		$0.00
337				436.92		$1,665.97		$0.00		$2,102.89		$2,247.22		$144.33
338				151.2		$852.20		$0.00		$1,003.40		$852.20		($151.20)
339				150.26				$0.00		$150.26		$144.10		($6.16)
340				460.32		$417.76		$0.00		$878.08		$417.76		($460.32)
341				451.08		$1,013.79		$0.00		$1,464.87		$1,464.87		$0.00
342										$5,000.00		$1,464.87		($3,535.13)

<tbody>
</tbody>

I did not understand the new requirement, in which table should work?
What formulas should be put and in which columns?
I think it does not have much to do with the thread of the original requirement, please could you create another thread.