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?


[TABLE="width: 583"]
<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>[TR]
[TD="width: 775, bgcolor: transparent, colspan: 8"]Worksheet 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]Credit[/TD]
[TD="bgcolor: transparent, colspan: 4"]Debit[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Date[/TD]
[TD="bgcolor: transparent"]Loan Number[/TD]
[TD="bgcolor: transparent"]Customer Name[/TD]
[TD="bgcolor: transparent"] Amount [/TD]
[TD="bgcolor: transparent"]Date[/TD]
[TD="bgcolor: transparent"]Loan Number[/TD]
[TD="bgcolor: transparent"]Customer Name[/TD]
[TD="bgcolor: transparent"]Amount [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]383894[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent"] $ 739.99 [/TD]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]28748[/TD]
[TD="bgcolor: transparent"]Kyle[/TD]
[TD="bgcolor: transparent"]$47.99[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]3456[/TD]
[TD="bgcolor: transparent"]Dale[/TD]
[TD="bgcolor: transparent"] $ 349.98 [/TD]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]133656[/TD]
[TD="bgcolor: transparent"]Skip[/TD]
[TD="bgcolor: transparent"]$7,834.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]6789[/TD]
[TD="bgcolor: transparent"]George[/TD]
[TD="bgcolor: transparent"] $ 392.55 [/TD]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]28497[/TD]
[TD="bgcolor: transparent"]Phil[/TD]
[TD="bgcolor: transparent"]$699.99[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]87263[/TD]
[TD="bgcolor: transparent"]Joe[/TD]
[TD="bgcolor: transparent"] $ 329.21 [/TD]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]87263[/TD]
[TD="bgcolor: transparent"]Tony[/TD]
[TD="bgcolor: transparent"]$884.98[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 8"]Worksheet 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Date[/TD]
[TD="bgcolor: transparent"]Loan Number[/TD]
[TD="bgcolor: transparent"]Customer Name[/TD]
[TD="bgcolor: transparent"]Amount 1[/TD]
[TD="bgcolor: transparent"]Amount 2[/TD]
[TD="bgcolor: transparent"]Total[/TD]
[TD="bgcolor: transparent"]Posted[/TD]
[TD="bgcolor: transparent"]Difference[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]64203[/TD]
[TD="bgcolor: transparent"]Abe[/TD]
[TD="bgcolor: transparent"]$199.99[/TD]
[TD="bgcolor: transparent"]$333.99[/TD]
[TD="bgcolor: transparent"]$533.98[/TD]
[TD="bgcolor: transparent"]$533.98[/TD]
[TD="bgcolor: transparent"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]87263[/TD]
[TD="bgcolor: transparent"]Joe[/TD]
[TD="bgcolor: transparent"]$2,188.23[/TD]
[TD="bgcolor: transparent"]$626.43[/TD]
[TD="bgcolor: transparent"]$2,814.66[/TD]
[TD="bgcolor: transparent"]$3,143.87[/TD]
[TD="bgcolor: transparent"]$329.21[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]28497[/TD]
[TD="bgcolor: transparent"]Phil[/TD]
[TD="bgcolor: transparent"]$3,199.87[/TD]
[TD="bgcolor: transparent"]$645.99[/TD]
[TD="bgcolor: transparent"]$3,845.86[/TD]
[TD="bgcolor: transparent"]$3,145.87[/TD]
[TD="bgcolor: transparent"]-$699.99[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]64203[/TD]
[TD="bgcolor: transparent"]John[/TD]
[TD="bgcolor: transparent"]$2,145.32[/TD]
[TD="bgcolor: transparent"]$9,834.09[/TD]
[TD="bgcolor: transparent"]$11,979.41[/TD]
[TD="bgcolor: transparent"]$11,979.41[/TD]
[TD="bgcolor: transparent"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]87263[/TD]
[TD="bgcolor: transparent"]Tony[/TD]
[TD="bgcolor: transparent"]$626.43[/TD]
[TD="bgcolor: transparent"]$884.98[/TD]
[TD="bgcolor: transparent"]$1,511.41[/TD]
[TD="bgcolor: transparent"]$626.43[/TD]
[TD="bgcolor: transparent"]-$884.98[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]28497[/TD]
[TD="bgcolor: transparent"]Al[/TD]
[TD="bgcolor: transparent"]$645.99[/TD]
[TD="bgcolor: transparent"]$645.99[/TD]
[TD="bgcolor: transparent"]$1,291.98[/TD]
[TD="bgcolor: transparent"]$1,291.98[/TD]
[TD="bgcolor: transparent"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19/2019[/TD]
[TD="bgcolor: transparent"]6789[/TD]
[TD="bgcolor: transparent"]George[/TD]
[TD="bgcolor: transparent"]$333.43[/TD]
[TD="bgcolor: transparent"]$0.00[/TD]
[TD="bgcolor: transparent"]$333.43[/TD]
[TD="bgcolor: transparent"]$725.98[/TD]
[TD="bgcolor: transparent"]$392.55[/TD]
[/TR]
</tbody>[/TABLE]

 

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

 

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

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.