Pull Data from One Sheet's Range to Another Sheet based on Criteria

NewFrugal

New Member
Joined
Jun 22, 2022
Messages
7
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
  2. Mobile
Hello. Please bare with me as this is my first post although I've visited as a guest sporadically over the years. I've tried researching how to automatically populate data from one sheet into another based on specific criteria, and I haven't been lucky in finding a suitable answer. Perhaps I'm not searching under the correct terms.

Specifically, I need to know how to automatically copy negative numbers from the original source sheet into a second sheet, and positive numbers into a third sheet. For clarity, I download daily bank transactions with many lines, and I figured there has to be a simpler way to do this rather than copying and pasting a lot of data. I need help having one sheet pull the posting date, reference, amount and class into a Debits sheet (negative numbers) and Credits sheet (positive). I'm assuming an advanced filter should be used, but I don't have unique data for this to easily occur. Shouldn't a sheet be able to look up a range of another, read it, and copy the specific criteria I'm seeking? I'm sorry if this still doesn't make sense. I've included a sample sheet of raw data from downloaded statements. Ideally, the Debits and Credits sheets should automatically update/refresh -- or at least do so with the click of a macro button-- each time I add new data to an existing table.

Sample Stmt Download.xlsx
EFGHIJKL
1Post DateReferenceAdditional ReferenceAmountDescriptionTypeClassText
26/1/2022 GE$29,166.67INCOMING WIRE TRANSFERWireMiscellaneous DepositGU
36/1/2022UKOGF FOUNDATION Payments $3.49ACH CREDITAchDevelopmentUKOGF FOUNDATION Payments
46/1/20225/3 BANKCARD SYS COMB. DEP. $275.50ACH CREDITAchBookstore5/3 BANKCARD SYS COMB. DEP. Worldpay
56/1/2022Virginia529 EDI PYMNTS $2,167.42ACH CREDITAchTuitionVirginia529 EDI PYMNTS
66/1/202253355335($2,290.99)CHECK PAIDCheckCheck
76/1/20226012215260122152($39,234.49)LOAN PAYMENTLoanLoan847,,AUTOMATIC LOAN PAY
86/2/2022Square Inc 220602P2 $48.25ACH CREDITAchEagles' WingsSquare Inc Bird Wings
96/2/2022Virginia529 EDI PYMNTS $1,100.00ACH CREDITAchTuitionVirginia529 EDI PYMNTS
106/2/2022SEMI MONTHLY FSA TRA($3,957.60)MISC. DEBITTransferFSASEMI MONTHLY FSA TRANSFER
116/2/2022WWEX Franchise H EP($277.54)PREAUTHORIZED ACH DEBITAchWWExWWEX Franchise
126/3/2022$103.46DEPOSITDepositDeposit
136/3/20223257-22 BB Merchan $67.66ACH CREDITAchBBMS3257-22 BB Merchan
146/3/2022AVIDPAY SERVICE AV($15.05)PREAUTHORIZED ACH DEBITAchAvidPay ACHAVIDPAY SERVICE AVIDPAY
156/3/2022GONZAGA COLLEGE MO($19,600.00)PREAUTHORIZED ACH DEBITAchACHGCHS
166/3/2022Voya Nat Trst182 SP($88,409.72)PREAUTHORIZED ACH DEBITAchPayrollVoya Nat Trst182
176/6/2022ACTIVE NETWORK, REGISTRATI $50,669.64ACH CREDITAchActive NetworkACTIVE NETWORK,
186/7/2022MERCHANTSERVCS BI($29.99)PREAUTHORIZED ACH DEBITAchBank FeesMERCHANTSERVCS BILLNG School Store
196/17/2022Square Inc 220617P2 $48.25ACH CREDITAchSquare DepositSquare Inc BirdWings
206/17/2022SMART LLC SMART LLC $37,287.31ACH CREDITAchSmart TuitionSMART LLC SMART LLC
06-2022 Daily Transactions
 

NewFrugal

New Member
Joined
Jun 22, 2022
Messages
7
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
  2. Mobile
You're welcome NewFrugal. I'm glad to have been able to assist.

Cheerio,
vcoolio.
Hi, vcoolio. Is there a code I could use to have the button scroll with the rest of my worksheet, or would you suggest simply having a shortcut key that refreshes it while still copying data into the other sheets? I sincerely appreciate your help!
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,355
Office Version
  1. 365
Platform
  1. Windows
Hello NewFrugal,

I'm not a fan of floating buttons and generally have people freeze the top row of their dataset and place the button above the 'freeze' line. Hence, when you scroll through your data, the button is always there.
Your idea of a short cut key instead of a button will work. However, if you prefer a floating button, this code could do what you're wanting:-

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
            CommandButton1.Top = Target.Top
            CommandButton1.Left = Target.Offset(, 2).Left
    End With

End Sub

You'll need to change the Excel name reference of the button, in this case CommandButton1 in the code, to the actual name of your button. You'll find this in the name bar directly above Columns A:C. You'll see it appear in the name bar by going to the Developer tab, clicking on Design Mode then clicking on the button. Click on Design Mode to exit once done.

To implement this code:-
- Right click on the "Transactions" sheet tab (or whichever sheet is your data input sheet).
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Go back to the sheet and test it. You should find that the button will float, so if you scroll down, for example, the button may not be visible but will appear again beside any cell that you click on, always level with the cell and offset two columns to the right.

I hope that this helps.

Cheerio,
vcoolio.
 
Upvote 0

NewFrugal

New Member
Joined
Jun 22, 2022
Messages
7
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
  2. Mobile
Hello NewFrugal,

I'm not a fan of floating buttons and generally have people freeze the top row of their dataset and place the button above the 'freeze' line. Hence, when you scroll through your data, the button is always there.
Your idea of a short cut key instead of a button will work. However, if you prefer a floating button, this code could do what you're wanting:-

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
    With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
            CommandButton1.Top = Target.Top
            CommandButton1.Left = Target.Offset(, 2).Left
    End With

End Sub

You'll need to change the Excel name reference of the button, in this case CommandButton1 in the code, to the actual name of your button. You'll find this in the name bar directly above Columns A:C. You'll see it appear in the name bar by going to the Developer tab, clicking on Design Mode then clicking on the button. Click on Design Mode to exit once done.

To implement this code:-
- Right click on the "Transactions" sheet tab (or whichever sheet is your data input sheet).
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Go back to the sheet and test it. You should find that the button will float, so if you scroll down, for example, the button may not be visible but will appear again beside any cell that you click on, always level with the cell and offset two columns to the right.

I hope that this helps.

Cheerio,
vcoolio.
I don't know why I didn't think to just freeze the top row. Thank you so much for the code! I'll try all three ways and see what my cohorts collectively prefer. Thanks again!
 
Upvote 0

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,355
Office Version
  1. 365
Platform
  1. Windows
You're welcome NewFrugal. Let us know how you get on.

Cheerio,
vcoolio.
 
Upvote 0

Forum statistics

Threads
1,186,774
Messages
5,959,713
Members
438,442
Latest member
azedin4

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
Top