0

This is what I have so far. I am trying to take the data from both sheets "Match Goal" and "Match Exempt" and store them in the "target" tab, but I don't want to take the header from both tabs as they match and would skew the data.

 ActiveSheet.Range("A:AT").AutoFilter Field:=33, Criteria1:= _
        "=Insurance"
    Cells.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Sheets("Sheet2").Select 'will be Sheet1 in deployment since all tabs are already named when we receive
    Sheets("Sheet2").Name = "Paygenus Exempt"
    Sheets("Sheet21").Select
    ActiveSheet.ShowAllData
    
    
    ActiveSheet.Range("A:AT").AutoFilter Field:=17, Criteria1:= _
        "=Accepting"
    ActiveSheet.Range("A:AT").AutoFilter Field:=5, Criteria1:=">9999", _
        Operator:=xlAnd
    ActiveSheet.Range("A:AT").AutoFilter Field:=33, Criteria1:="<>Insurance", _
        Operator:=xlAnd
    Cells.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Sheets("Sheet3").Select 'will be Sheet2 in deployment since all tabs are already named when we receive
    Sheets("Sheet3").Name = "Match Goal"
    Sheets("Sheet21").Select
    ActiveSheet.ShowAllData
    
    
    ActiveSheet.Range("A:AT").AutoFilter Field:=17, Criteria1:=Array( _
        "=Not Accepting", "Not Found"), Operator:=xlFilterValues
    ActiveSheet.Range("A:AT").AutoFilter Field:=33, Criteria1:="<>Insurance", _
        Operator:=xlAnd
    Cells.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Sheets("Sheet4").Select 'Will be Sheet3 in deployment
    Sheets("Sheet4").Name = "Match Exempt"
    Cells.Copy
    
    
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Sheets("Sheet5").Select 'will be Sheet4 in deployment since all tabs are already named when we receive
    Sheets("Sheet5").Name = "Target"
    Sheets("Match Goal").Select
    Cells.Copy.Offset (1)
    Sheets("Target").Select
Christian Lance
  • 13
  • 3
  • It is also important to note that the data entered is going to be dynamic and change every time the macro is ran – Christian Lance Nov 23 '20 at 19:00
  • 1
    Before anything else I suggest you read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Nov 23 '20 at 19:06

0 Answers0