0

enter image description hereExcel spreadsheet

Criteria for moving rows are based on column A = "Advia 1800 2" and column B = "15941".

If criteria are met, then copy and paste these rows to column I on the same worksheet under the headings as in the attached picture.

I really appreciate if someone can show me what the macro will look like.

Community
  • 1
  • 1
Gary
  • 17
  • 7

1 Answers1

0

  1. You also have an additional friend and that one is Macro Recorder. You can start recording your actions by selecting Record Macro It can be selected from Views->Macros-> Record Macro in Views Tab or in Developer Tab in code section or by clicking its symbol near bottom left corner side of your worksheet.

  2. Now after clicking record macro, a popup appears and you can okay it after either noting macro name or change it to new one. Let us ignore shortcut key at this stage.

  3. Now Macro has started recording your actions do manually what you want to do. Select Range to be filtered, Select Filter in Data Tab. Apply criteria from drop downs in Column 1 and then in Column 2. Go to Special in Find and Select section drop down and select visible cells and then copy. Select I1 cell and Paste. Go to Data Tab and click Filter again. Now you will see results in the sheet as per your requirements. Code generated by Macro recorder is:

     Sub Macro5()   

    '
    ' Macro5 Macro
    '

    '
    Range("A1:G14").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$14").AutoFilter Field:=1, Criteria1:= _
    "Advia 1800 2"
    ActiveSheet.Range("$A$1:$G$14").AutoFilter Field:=2, Criteria1:="15941"
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Range("I1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
End Sub

Results are shown in the snapshot.

Results

Still no reason to cheer as it has select statements. Please go through How to avoid using Select in Excel VBA macros as an exercise for you and modify the code to remove select in macro.

Community
  • 1
  • 1
skkakkar
  • 2,690
  • 2
  • 15
  • 29
  • @Gary I am Glad that it worked for you. Please accept the Answer by ticking a green check mark below the lower triangle on the left side of the answer. This is the one thing which you can only do. It is also SO way of conveying thanks. – skkakkar Jun 18 '16 at 02:38
  • Brilliant! I hope this will help others. It certainly helped me a lot. – Gary Jun 18 '16 at 16:55
  • @Gary Keep it up. You will go a long way. Thanks. – skkakkar Jun 18 '16 at 16:56
  • Cheers skkakkar! Thanks for your encouragement. I think at the end of this project, a lot of people from scientific field will benefit from it. We work with so many quality data and graph, but now I want to make this process automated. The challenge will come when I transform the above data into a graph. I am thinking already of recording the macros as you had shown me. I will need probably need your help again. I will post my question on stack. – Gary Jun 18 '16 at 17:06