Balance Sheet - Bring In Numbers From Other Tab/Sheet

R

richmcgill

Board Regular
Joined
Feb 4, 2019
Messages
86
Office Version
  1. 2016
Platform
  1. Windows
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]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try this
Assuming that the data in both sheets start in cell A1

Code: 
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
 
Upvote 0
Thank you very much for the help.

I really do appreciate everyone taking the time to help.
 
Upvote 0
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]
 
Last edited:
Upvote 0
richmcgill said:
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]
Click to expand...


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Color Code Cells VBA Macro
Replies
5
Views
923
richmcgill
R
N
  • Question Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
2K
Logit
Logit
R
  • Question Question
Date Count Based on Date in Another Cell
Replies
1
Views
748
Joe4
Joe4
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
P
  • Question Question
Convert duplicate number / reference unique if is 8 or less digits
Replies
3
Views
679
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top