Hello
I am trying to create a macro that can count how many times a stock shows up during a particular day. It needs to do a few things:
1. Filter for prior day or most recent date in column N
2. Filter column H for Volatility; its what the stock has been categorized as
3. Remove duplicates from column D, which is the name of the stocks, to show only single values
4. Show a count of how many symbols
I created the following macro but it only works just on the spreadsheet I created. I feel like it has to do with the range some of my spreadsheets have more lines than others. I've tried expanding it but still doesn't work on other spreadsheets. Any suggestions would be great
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$77081").AutoFilter Field:=14, Operator:= _
xlFilterValues, Criteria2:=Array(2, Date - 1)
ActiveSheet.Range("$A$1:$N$77081").AutoFilter Field:=8, Criteria1:= _
"Volatility"
Columns("D:D").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$976574").RemoveDuplicates Columns:=1, Header:= _
xlYes
Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTA(RC[-1]:R[998]C[-1])"
I am trying to create a macro that can count how many times a stock shows up during a particular day. It needs to do a few things:
1. Filter for prior day or most recent date in column N
2. Filter column H for Volatility; its what the stock has been categorized as
3. Remove duplicates from column D, which is the name of the stocks, to show only single values
4. Show a count of how many symbols
I created the following macro but it only works just on the spreadsheet I created. I feel like it has to do with the range some of my spreadsheets have more lines than others. I've tried expanding it but still doesn't work on other spreadsheets. Any suggestions would be great
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$77081").AutoFilter Field:=14, Operator:= _
xlFilterValues, Criteria2:=Array(2, Date - 1)
ActiveSheet.Range("$A$1:$N$77081").AutoFilter Field:=8, Criteria1:= _
"Volatility"
Columns("D:D").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$976574").RemoveDuplicates Columns:=1, Header:= _
xlYes
Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTA(RC[-1]:R[998]C[-1])"
