Automatic copy of rows from either of two sheets to a third
Closed
melrowgo
Posts
1
Registration date
Monday June 12, 2017
Status
Member
Last seen
June 12, 2017
-
Updated on Jun 12, 2017 at 10:45 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jun 13, 2017 at 06:42 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jun 13, 2017 at 06:42 AM
Related:
- Automatic copy of rows from either of two sheets to a third
- Nvidia automatic driver detection tool - Guide
- Sheets right to left - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to take screenshot of excel sheet on laptop - Guide
- How to automatically save photos from messenger to gallery - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Updated on Jun 13, 2017 at 06:57 AM
Updated on Jun 13, 2017 at 06:57 AM
Hello Melrowgo,
If I have understood your query correctly, I think that the following code may do the task for you:-
Following is the link to a little sample that I have prepared for you from my understanding of your opening post:-
https://www.dropbox.com/s/uxvbzuqcwm4db34/Deposits%26Expenses%20Summary%28Melrowgo%29.xlsm?dl=0
Click on the "Summarise" button to see it all work.
You'll notice in the sample that I have added a "Type" column in each sheet ("D" for Deposit and "E" for Expense) just to make identifying the type of expense in the "Register" sheet easier for the code to manipulate.
In the "Register" sheet, should the Running Balance go into the "red" then the font will change to red for simpler identification of the fact.
The "Register" sheet will refresh each time that you click on the button.
Play with the sample by adding/deleting entries as you wish so that you can see how the code copes with the changes but test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
P.S. If you want to sort the "Register" by date, just add the following line of code:-
directly after line 25 in the code above.
If I have understood your query correctly, I think that the following code may do the task for you:-
Sub Summarise() Dim lr As Long Dim ws As Worksheet, ws1 As Worksheet, sh As Worksheet Set ws = Sheet1 Set ws1 = Sheet2 Set sh = Sheet3 Application.ScreenUpdating = False sh.UsedRange.Offset(1).ClearContents lr = ws.Range("A" & Rows.Count).End(xlUp).Row If lr > 1 Then Union(ws.Range("A2:B" & lr), ws.Range("M2:N" & lr)).Copy sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues End If lr = ws1.Range("A" & Rows.Count).End(xlUp).Row If lr > 1 Then Union(ws1.Range("A2:B" & lr), ws1.Range("P2:Q" & lr)).Copy sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues End If lr = sh.Range("A" & Rows.Count).End(xlUp).Row sh.[E2] = "=C2" sh.Range("E3:E" & lr) = "=$C3+$E2" For i = 2 To lr If sh.Cells(i, 4).Value = "E" Then sh.Cells(i, 3) = "-" & sh.Cells(i, 3).Value If Left(sh.Cells(i, 5), 1) = "-" Then sh.Cells(i, 5).Font.ColorIndex = 3 Else: sh.Cells(i, 5).Font.ColorIndex = 1 End If End If Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
Following is the link to a little sample that I have prepared for you from my understanding of your opening post:-
https://www.dropbox.com/s/uxvbzuqcwm4db34/Deposits%26Expenses%20Summary%28Melrowgo%29.xlsm?dl=0
Click on the "Summarise" button to see it all work.
You'll notice in the sample that I have added a "Type" column in each sheet ("D" for Deposit and "E" for Expense) just to make identifying the type of expense in the "Register" sheet easier for the code to manipulate.
In the "Register" sheet, should the Running Balance go into the "red" then the font will change to red for simpler identification of the fact.
The "Register" sheet will refresh each time that you click on the button.
Play with the sample by adding/deleting entries as you wish so that you can see how the code copes with the changes but test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
P.S. If you want to sort the "Register" by date, just add the following line of code:-
sh.Range("A2", sh.Range("D" & sh.Rows.Count).End(xlUp)).Sort sh.[A2], 1
directly after line 25 in the code above.