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

[NewFrugal]

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
	E	F	G	H	I	J	K	L
1	Post Date	Reference	Additional Reference	Amount	Description	Type	Class	Text
2	6/1/2022	GE		$29,166.67	INCOMING WIRE TRANSFER	Wire	Miscellaneous Deposit	GU
3	6/1/2022	UKOGF FOUNDATION Payments		$3.49	ACH CREDIT	Ach	Development	UKOGF FOUNDATION Payments
4	6/1/2022	5/3 BANKCARD SYS COMB. DEP.		$275.50	ACH CREDIT	Ach	Bookstore	5/3 BANKCARD SYS COMB. DEP. Worldpay
5	6/1/2022	Virginia529 EDI PYMNTS		$2,167.42	ACH CREDIT	Ach	Tuition	Virginia529 EDI PYMNTS
6	6/1/2022	5335	5335	($2,290.99)	CHECK PAID	Check	Check
7	6/1/2022	60122152	60122152	($39,234.49)	LOAN PAYMENT	Loan	Loan	847,,AUTOMATIC LOAN PAY
8	6/2/2022	Square Inc 220602P2		$48.25	ACH CREDIT	Ach	Eagles' Wings	Square Inc Bird Wings
9	6/2/2022	Virginia529 EDI PYMNTS		$1,100.00	ACH CREDIT	Ach	Tuition	Virginia529 EDI PYMNTS
10	6/2/2022	SEMI MONTHLY FSA TRA		($3,957.60)	MISC. DEBIT	Transfer	FSA	SEMI MONTHLY FSA TRANSFER
11	6/2/2022	WWEX Franchise H EP		($277.54)	PREAUTHORIZED ACH DEBIT	Ach	WWEx	WWEX Franchise
12	6/3/2022			$103.46	DEPOSIT	Deposit	Deposit
13	6/3/2022	3257-22 BB Merchan		$67.66	ACH CREDIT	Ach	BBMS	3257-22 BB Merchan
14	6/3/2022	AVIDPAY SERVICE AV		($15.05)	PREAUTHORIZED ACH DEBIT	Ach	AvidPay ACH	AVIDPAY SERVICE AVIDPAY
15	6/3/2022	GONZAGA COLLEGE MO		($19,600.00)	PREAUTHORIZED ACH DEBIT	Ach	ACH	GCHS
16	6/3/2022	Voya Nat Trst182 SP		($88,409.72)	PREAUTHORIZED ACH DEBIT	Ach	Payroll	Voya Nat Trst182
17	6/6/2022	ACTIVE NETWORK, REGISTRATI		$50,669.64	ACH CREDIT	Ach	Active Network	ACTIVE NETWORK,
18	6/7/2022	MERCHANTSERVCS BI		($29.99)	PREAUTHORIZED ACH DEBIT	Ach	Bank Fees	MERCHANTSERVCS BILLNG School Store
19	6/17/2022	Square Inc 220617P2		$48.25	ACH CREDIT	Ach	Square Deposit	Square Inc BirdWings
20	6/17/2022	SMART LLC SMART LLC		$37,287.31	ACH CREDIT	Ach	Smart Tuition	SMART LLC SMART LLC
06-2022 Daily Transactions

 

[NewFrugal]

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!

 

[vcoolio]

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:-

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.

 

[NewFrugal]

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:-

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!

 

[vcoolio]

You're welcome NewFrugal. Let us know how you get on.

Cheerio,
vcoolio.