Copy data from Filtered data specific dated to another excel sheet

V

vinay1513

New Member
Joined
Sep 16, 2023
Messages
3
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hello Sir,

I have daily routine work. I am creating bank position from Bank Statement.
I have 2 files
(1) Bank Statement (from Data Copy)
(2) Bank Position (where data Paste)

I would like to create a macro which is filter bank statement data. Select and Copy data from specify date & credit/debit amount range an paste in bank position file in specific place receipt/payments.
 

Attachments

  • Bank Position.JPG
    Bank Position.JPG
    77.1 KB · Views: 12
  • Bank Statement.JPG
    Bank Statement.JPG
    140.8 KB · Views: 12

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
vinay1513 said:
Hello Sir,

I have daily routine work. I am creating bank position from Bank Statement.
I have 2 files
(1) Bank Statement (from Data Copy)
(2) Bank Position (where data Paste)

I would like to create a macro which is filter bank statement data. Select and Copy data from specify date & credit/debit amount range an paste in bank position file in specific place receipt/payments.
Click to expand...
Can you please submit your bank download and what you want to produce using XL2BB?

Names of workbooks and worksheets would be useful too.
 
Upvote 0
Herakles said:
Can you please submit your bank download and what you want to produce using XL2BB?

Names of workbooks and worksheets would be useful too.
Click to expand...
This is my Bank Statement of 14/09/2023 and 15/09/2023

Report-20230916091214.csv
ABCDEFGHI
1Account Statement
2ALLAND AND SAYAJI LLP
361 GUJARAT VEPARI MAHAMANDALCust. Reln. No.
4ODHAV ROAD ODHAVAccount No.
5.PeriodFrom 16/06/2023 To 16/09/2023
6AhmedabadCurrencyINR
7GUJARATBranchAHMEDABAD - ODHAV
8INDIANomination RegdN
9382410Nominee Name
10
11Sl. No.DateDescriptionChq / Ref numberValue DateWithdrawalDepositBalanceCR/DR
12115/09/2023 20:51Chrg: NEFT On 05-Sep-2023TBMS-1263104060#94.472,35,398.72CR
13215/09/2023 20:48NEFT PUNBH23258933964 NICE CONFECTIONERY PUNB041NEFTINW-0664054855#1,06,000.0072,35,493.12CR
14315/09/2023 18:53NEFT 325801880GN00084 GLAXOSMITHKLINE PHARMACEUTINEFTINW-0663989556#2,37,074.0071,29,493.12CR
15415/09/2023 16:48NEFT-JUHF CERTIFICATION P-CMS2582373005834FCM-2309155LRPMQ#13,769.0068,92,419.12CR
16515/09/2023 15:59UPI/CHAUHAN VISHAL/325847531287/Vishal ChauhanUPI-325899172670#5,000.0069,06,188.12CR
17615/09/2023 12:08RTGS BNPAR52023091500478682 MANE KANCOR INGREDIRTGSINW-0064693217#9,32,400.0069,01,188.12CR
18714/09/2023 17:54NEFT BARBD23257345353 DUTY DRAWBACK BARB0JAMIYANEFTINW-0663272566#9,959.0059,68,788.12CR
19814/09/2023 17:32NEFT BARBD23257344855 B C M S SANAND BARB0SANANDNEFTINW-0663265262#8,705.0059,58,829.12CR
20914/09/2023 17:32NEFT BARBD23257344857 B C M S SANAND BARB0SANANDNEFTINW-0663265261#8,705.0059,50,124.12CR
211014/09/2023 10:35UPI/VIKRAMSINH KODA/325776533232/UPIUPI-325757906243#2,000.0059,41,419.12CR
221114/09/2023 10:20IB: ETAX TIN2 0027371499GBM-0027371499#25,00,000.0059,39,419.12CR
231214/09/2023 10:20IB: ETAX TIN2 0027371389GBM-0027371389#25,00,000.0084,39,419.12CR
241314/09/2023 10:20IB: ETAX TIN2 0027371292GBM-0027371292#25,00,000.001,09,39,419.12CR
251414/09/2023 10:19IB: ETAX TIN2 0027371306GBM-0027371306#15,00,000.001,34,39,419.12CR
261514/09/2023 10:19IB: ETAX BILL DESK PAYME 0027371118GBM-0027371118#6,404.001,49,39,419.12CR
Report-20230916091214
Cell Formulas
RangeFormula
A3A3="61 GUJARAT VEPARI MAHAMANDAL"
A4A4="ODHAV ROAD ODHAV"
A5A5="."
A9A9="382410"
C12C12="Chrg: NEFT On 05-Sep-2023"
D12D12="TBMS-1263104060"
C13C13="NEFT PUNBH23258933964 NICE CONFECTIONERY PUNB041"
D13D13="NEFTINW-0664054855"
C14C14="NEFT 325801880GN00084 GLAXOSMITHKLINE PHARMACEUTI"
D14D14="NEFTINW-0663989556"
C15C15="NEFT-JUHF CERTIFICATION P-CMS2582373005834"
D15D15="FCM-2309155LRPMQ"
C16C16="UPI/CHAUHAN VISHAL/325847531287/Vishal Chauhan"
D16D16="UPI-325899172670"
C17C17="RTGS BNPAR52023091500478682 MANE KANCOR INGREDI"
D17D17="RTGSINW-0064693217"
C18C18="NEFT BARBD23257345353 DUTY DRAWBACK BARB0JAMIYA"
D18D18="NEFTINW-0663272566"
C19C19="NEFT BARBD23257344855 B C M S SANAND BARB0SANAND"
D19D19="NEFTINW-0663265262"
C20C20="NEFT BARBD23257344857 B C M S SANAND BARB0SANAND"
D20D20="NEFTINW-0663265261"
C21C21="UPI/VIKRAMSINH KODA/325776533232/UPI"
D21D21="UPI-325757906243"
C22C22="IB: ETAX TIN2 0027371499"
D22D22="GBM-0027371499"
C23C23="IB: ETAX TIN2 0027371389"
D23D23="GBM-0027371389"
C24C24="IB: ETAX TIN2 0027371292"
D24D24="GBM-0027371292"
C25C25="IB: ETAX TIN2 0027371306"
D25D25="GBM-0027371306"
C26C26="IB: ETAX BILL DESK PAYME 0027371118"
D26D26="GBM-0027371118"
 
Upvote 0
Herakles said:
Can you please submit your bank download and what you want to produce using XL2BB?

Names of workbooks and worksheets would be useful too.
Click to expand...
This is my output file which i have to create.
I am doing deposit entry copy and paste in Receipts and Payment Entries Paste in Payment.

KOTAK BANK ALLAND & SAYAJI.xlsx
BCDEF
39879ALLAND & SAYAJI LLP C.C. A/C No. 0412413382
39880Daily Bank Report - Kotak Mahindra Bank
39881Dated:15.09.23
39882Drawing power as per Stock Statement of FEBRUARY - 20233,68,63,870.00
39883Available Limit3,68,63,870.00
3988414.09.23Opening Balance1,49,45,823.12
39885Add: Payments
39886DateParty NameModeAmount Total Amt
3988714/09/2023IB: ETAX TIN2 0027371499IB25,00,000.00
3988814/09/2023IB: ETAX TIN2 0027371389IB25,00,000.00
3988914/09/2023IB: ETAX TIN2 0027371292IB25,00,000.00
3989014/09/2023IB: ETAX TIN2 0027371306IB15,00,000.00
3989114/09/2023IB: ETAX BILL DESK PAYME 0027371118IB6,404.00
39892
3989390,06,404.00
3989459,39,419.12
39895Less: Receipts
39896DateParty NameModeAmountTotal Amt
3989714/09/2023NEFT BARBD23257345353 DUTY DRAWBACK BARB0JAMIYANEFT9,959.00
3989814/09/2023NEFT BARBD23257344855 B C M S SANAND BARB0SANANDNEFT8,705.00
3989914/09/2023NEFT BARBD23257344857 B C M S SANAND BARB0SANANDNEFT8,705.00
3990014/09/2023UPI/VIKRAMSINH KODA/325776533232/UPIUPI2,000.00
39901
3990229,369.00
3990314.09.23Closing Balance59,68,788.12
3990414.09.23Available Balance4,28,32,658.12
39905
39906
39907
39908ALLAND & SAYAJI LLP C.C. A/C No. 0412413382
39909Daily Bank Report - Kotak Mahindra Bank
39910Dated:16.09.23
39911Drawing power as per Stock Statement of FEBRUARY - 20233,68,63,870.00
39912Available Limit3,68,63,870.00
3991315.09.23Opening Balance59,68,788.12
39914Add: Payments
39915DateParty NameModeAmount Total Amt
3991615/09/2023Chrg: NEFT On 05-Sep-2023NEFT94.40
3991715/09/2023NEFT-JUHF CERTIFICATION P-CMS2582373005834NEFT13,769.00
39918
3991913,863.40
3992059,54,924.72
39921Less: Receipts
39922DateParty NameModeAmountTotal Amt
3992315/09/2023NEFT PUNBH23258933964 NICE CONFECTIONERY PUNB041NEFT1,06,000.00
3992415/09/2023NEFT 325801880GN00084 GLAXOSMITHKLINE PHARMACEUTINEFT2,37,074.00
3992515/09/2023UPI/CHAUHAN VISHAL/325847531287/Vishal ChauhanUPI5,000.00
3992615/09/2023RTGS BNPAR52023091500478682 MANE KANCOR INGREDIRTGS9,32,400.00
39927
3992812,80,474.00
3992915.09.23Closing Balance72,35,398.72
3993015.09.23Available Balance4,40,99,268.72
Sheet1
Cell Formulas
RangeFormula
F39884,F39913F39884=F39874
F39893F39893=SUM(E39887:E39892)
F39894F39894=+F39884-F39893
B39904,B39930B39904=B39903
F39902,F39928F39902=SUM(E39897:E39901)
F39903,F39929F39903=+F39894+F39902
F39904F39904=+F39883+F39903
F39919F39919=SUM(E39916:E39918)
F39920F39920=+F39913-F39919
F39930F39930=+F39912+F39929
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Pull exchange data from another workbook
Replies
2
Views
76
Maniac189
M
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs
D
  • Question Question
Remove matching rows with pause function
Replies
1
Views
182
alansidman
A
N
  • Question Question
Auto Allocate Account from the Master Sheet
Replies
6
Views
463
nitkot
N
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
856
Micron
M

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

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