Making A Dynamic Macro: Sort based on any value. filter, then copy and paste to a result tab (Multiple Data Sets)

S

sticky bandits

New Member
Joined
Jun 22, 2017
Messages
2
Hello,
I am fairly new to Macros but I will try to explain my question to the best of my abilities. I am working on a project where I am given multiple data sets that I must filter the Zscore (A header within the data) to be greater than or equal to, or less than or equal to a certain value. Each data set has a zcore. First, I filter the one selection to be greater than or equal to the value, then copy and paste the resulting selection to a "results" tab. I then go back, unfilter the first data set, then apply the same filter to the next and repeat the process. I then repeat this process for the same data, however, now the criteria is based on a filter where the Zscore is less than or equal to. I was now asked to make the macro more dynamic in the sense that one can put any value in a box where the Zscore criteria is listed (Zscore>= X and Zscore<=X) and the macro will automatically pull this value and filter the criteria based on said value, then copy the selection and past to the respective areas. I have searched for a few days looking for an answer and I was not able to find one. If someone could please help me with problem or refer me to post that explains this, it would be very appreciated. Thank you.
Please see below the code for the first criteria (greater than or equal to), followed by the code for the second criteria I made (Less than or equal to). Sorry if the code is terribly written, I have been teaching myself Macros and VB and I am not quite that good yet. If helpful, I could post the excel file I am working from(not sure if it is allowed on the site) or any other information in order to make this as easy as possible, please just let me know. Thanks again.

Code: 
Sub G()
'
' G Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
    ActiveSheet.Range("A4").Select
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=4, Criteria1:=">=1.95", _
        Operator:=xlAnd
    Range("A12:R12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    Range("S4").Select
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=4
    Range("V5").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=14, Criteria1:=">=1.95" _
        , Operator:=xlAnd
    Range("A5").Select
    Application.CutCopyMode = False
    Range("A5:H5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Zscore Summary").Select
    ActiveSheet.Paste
    Sheets("Data").Select
    Range("S5:W5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Next.Select
    Range("AA4").Select
    ActiveSheet.Paste
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=14
    Application.CutCopyMode = False
    Range("E35").Select
    Sheets("Zscore Summary").Select
    Range("U18").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    Range("AK7").Select
End Sub
Sub L()
'
' L Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=4
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=4, Criteria1:="<=1.5", _
        Operator:=xlAnd
    Range("A5:R5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Zscore Summary").Select
    Range("AM4").Select
    ActiveSheet.Paste
    Range("BE4").Select
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=4
    Range("Q24").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=14, Criteria1:="<=1.5", _
        Operator:=xlAnd
    Range("A12:H12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Zscore Summary").Select
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 42
    ActiveSheet.Paste
    Sheets("Data").Select
    Range("S12:AB12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Zscore Summary").Select
    Range("BM4").Select
    ActiveSheet.Paste
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=14
    Range("P12").Select
    Application.CutCopyMode = False
    Sheets("Zscore Summary").Select
    Range("BL18").Select
End Sub

Code: 
Sub L()
'
' L Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=4
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=4, Criteria1:="<=1.5", _
        Operator:=xlAnd
    Range("A5:R5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Zscore Summary").Select
    Range("AM4").Select
    ActiveSheet.Paste
    Range("BE4").Select
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=4
    Range("Q24").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=14, Criteria1:="<=1.5", _
        Operator:=xlAnd
    Range("A12:H12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Zscore Summary").Select
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 42
    ActiveSheet.Paste
    Sheets("Data").Select
    Range("S12:AB12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Zscore Summary").Select
    Range("BM4").Select
    ActiveSheet.Paste
    Sheets("Data").Select
    ActiveSheet.Range("$I$4:$AB$11").AutoFilter Field:=14
    Range("P12").Select
    Application.CutCopyMode = False
    Sheets("Zscore Summary").Select
    Range("BL18").Select
End Sub
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Two criterias in a column.
Code: 
Sub Sample()
    Range("A1").AutoFilter Field:=1, Criteria1:="<100",Operator:=xlAnd, Criteria2:=">40"
End Sub

I just tried to modify your code but it was no good...(Only the first module)

Code: 
Sub G()
Dim wsD As Worksheet, wsZ As Worksheet, wsR As Worksheet
Set wsD = Sheets("Data")
Set wsZ = Sheets("Zscore Summary")
Set wsR = Sheets("result")

With wsD
    .Range("I4").AutoFilter Field:=4, Criteria1:=">=1.95"
    .Range(.Range("A5"), .Range("A4").SpecialCells(xlCellTypeLastCell)).Resize(, 23).copy
    wsR.Range("A1").PasteSpecial Paste:=xlPasteValues
    .ShowAllData
    .Range("$I$4:$AB$11").AutoFilter Field:=14, Criteria1:=">=1.95"
    .Range(.Range("A5"), .Range("A4").SpecialCells(xlCellTypeLastCell)).Resize(, 8).copy
    wsZ.Range("A1").PasteSpecial Paste:=xlPasteValues
    .Range("S5").CurrentRegion.copy wsR.Range("AA4")
End With
wsZ.Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K
G
  • Solved
How do I automatically insert into next blank row..
Replies
2
Views
664
ghrek
G
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
1K
cstuder
C
M
  • Solved
Replacing delete with a value
Replies
3
Views
397
Alex Blakenburg
A
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
407
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,746
Messages
6,180,705
Members
452,994
Latest member
Janick

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