0

I have macro to copy data based on certain filters and paste it into a template.

Column J to be copied to Column A.
Column C to be copied to Column B.
Column D to be copied to Column E.
Column H to be copied to Column F. 

Sub DS()

    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$O$5000").AutoFilter Field:=12, Criteria1:="PENDING"
    ActiveWindow.SmallScroll Down:=-12
    ActiveSheet.Range("$A$1:$O$5000").AutoFilter Field:=10, Criteria1:="U3R"
    ActiveWindow.SmallScroll Down:=-36
    Windows("Transfers 2020 - Roy.xlsm").Activate
    Range("J469:J537").Select
    Selection.Copy
    Windows("template2.xlsx").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Windows("Transfers 2020 - Roy.xlsm").Activate
    ActiveWindow.SmallScroll Down:=-15
    Range("C469:C537").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("template2.xlsx").Activate
    Range("B1").Select
    ActiveSheet.Paste
    Windows("Transfers 2020 - Roy.xlsm").Activate
    ActiveWindow.SmallScroll Down:=-15
    Range("D469:D537").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("template2.xlsx").Activate
    Range("E1").Select
    ActiveSheet.Paste
    Range("F2:J2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F3:J18").Select
    ActiveSheet.Paste
    Range("C1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=CONCAT(RC[2],RC[-1],RC[-2],2262020)"
    Range("C1").Select
    Selection.Copy
    Range("C1:C18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1:L19").Select
    Application.CutCopyMode = False
    Selection.ClearFormats
    Range("C7").Select
    ChDir "H:\A\Roy\B\DS"
    ActiveWorkbook.SaveAs Filename:= _
        "H:\A\Roy\B\DS\DS 2.26.2020.txt", FileFormat _
        :=xlText, CreateBackup:=False
    ActiveWindow.Close
End Sub

Question 1 : For the Criteria "PENDING" and "U3R" I want it to go through the whole range and look for "PENDING".

Question 2: I want to add an OR condition with "U3R" For Eg. "U3R" OR "U2R"

Question 3 : I only want to select the cells which are left after the above filters are applied. What changes should I make to this ? "Range("J469:J537").Select"

Thank you in advance!

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Roy
  • 85
  • 8
  • Hi @RicardoDiaz – Roy Feb 26 '20 at 19:46
  • 2
    Side note: You generally want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Feb 26 '20 at 19:47
  • 2
    Thanks for letting us know what you want. What have you tried? Did what you tried not work? If not, what issue did you run into with what you tried? Also, please keep your questions to one per post. – braX Feb 26 '20 at 19:57
  • You also want to avoid `ActiveSheet`. And `ActiveWindow`. And unqualified `Range`, which is a shorthand for `ActiveSheet.Range`. What happens if the user switches sheets or windows in the middle of your code? – Zev Spitz Feb 26 '20 at 20:52
  • Hi @ZevSplitz - Nothing happens when I switch sheets. I just want to copy columns based on the filter, add it to the template and save it as a txt file. – Roy Feb 27 '20 at 14:48

0 Answers0