Posting here as it appears there is no method of sending attachments via PM.
A couple of things. The macro won't check to see if the code already appears in the list, so everytime the list is updated any "BUY" rows will copied over (that can be fixed relatively easily with a pivot table or just turning on auto filters. "BUY" is case sensitive, so "buy" will not be copied over. Anytime Sheet1 is modified the macro will run. It's running pretty fast at the moment.
Any questions let me know...
Code below, with my (probably less than helpful!) explanations:
Code:Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Dim xRange As Range Dim cl As Range ' Set the range in Sheet1 to be searched. In this instance the range is each cell in column A with data in it With Worksheets("sheet1") Set xRange = .Range("a2", .Range("a2").End(xlDown)) End With ' Once the range is defined, for each cell in column A an IF statement is used to determine ' if the text string is "BUY" (nb this is case sensitive) ' If a "BUY" is found, then the entire row is copied into the first free row in "Sheet2" For Each cl In xRange If cl.Offset(0, 1).Value = "BUY" Then cl.EntireRow.Copy Destination:=Sheets("Sheet2").Range("a3000").End(xlUp).Offset(1, 0) Next cl Application.ScreenUpdating = True End Sub
Thanks for your efforts McLovin.
I have to wait to get back on a computer with excel (unfortunately I don't have it on this one) before I can try it out.
Apologies if this has been asked before.
I'm trying to speed up the process of my tax return from my trading account but my broker is lazy and aggregates trades then does a currency conversion on the aggregate. I need to know the AU amount of each row ie do a currency conversion for each row. But I have the conversion number in the E column and want to just copy this number to column M say, as per pic below.
This is basically what I want to 'code' -
IF column E starts with Transfer THEN copy the last 6 digits of column E to column M
Thanks in advance
Statement question for asf.jpg
ETA: pixel beat me...
Wow - now that's service - thank you both
In Excel I normally apply Filters to my table headings and those drop down lists allow me to easily rank the data.
Now if I have a table with streaming data coming in - is there a way to auto-sort a table?
E.g. My table has % change for ASX 200 stocks being streamed in, and I want the top movers to always appear on the top without me having to actually click the little filter arrow and select "Sort smallest to largest".
Good few weeks there for you TH
Anyone use Kinetick as a data provider? Just swapped over from eSignal and am struggling to import data from them into excel.
Hello excel gurus. if I have a data series plotted on a bar graph such as this,
='P & L'!$B$83:$B$120,'P & L'!$D$83:$D$120
Is there a way to exclude one cell, being D103?