Filter, Copy, Paste, Array Formulas

[nirvehex]

Hi,

I'm trying to write some VBA code (and it's been awhile) to do the following:

I have a tab called 'WOs' and another tab called 'WOs (DSP Only)'.

I'm trying to first filter on the 'WOs' tab on column P to select only "DSP" and "REC".

Then I'd like to copy all data on the 'WOs' tab and paste as values starting in cell G1 on the 'WOs (DSP Only)' tab.

Then on the 'WOs (DSP Only)' tab I'm trying to have the following formulas and drag them all the way down to the last row:

In A2: =CONCATENATE(L2,"-",K2,"-",Z2,"-",X2) and copy all the way down to last row where there is data in column G
In B2: {=PERCENTILE.INC(IF($A$2:$A$2=A2,$AF$2:$AF$2),0.5)} and copy all the way down to last row where there is data in column G
In C2: {=PERCENTILE.INC(IF($A$2:$A$2=A2,$AF$2:$AF$2),0.65)} and copy all the way down to last row where there is data in column G
In D2: {=PERCENTILE.INC(IF($A$2:$A$2=A2,$AF$2:$AF$2),0.75)} and copy all the way down to last row where there is data in column G
In E2: {=PERCENTILE.INC(IF($A$2:$A$2=A2,$AF$2:$AF$2),0.9)} and copy all the way down to last row where there is data in column G
In F2: {=PERCENTILE.INC(IF($A$2:$A$2=A2,$AF$2:$AF$2),0.99)} and copy all the way down to last row where there is data in column G

I know there has to be some VBA to do this and I have created part of it:

Sub WODSPTab()
'
' WODSPTab Macro
'


'Filter
 Worksheets("WOs").Range("A1").AutoFilter _
  Field:=16, Criteria1:="=DSP" _
        , Operator:=xlOr, Criteria2:="=REC"




'Paste Data from WO's Tab to WOs (DSP Only) tab


    Sheets("WOs").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight).End(xlDown)).Select
    Selection.Copy
    Sheets("WOs (DSP Only)").Select
    Range("G1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        
End Sub

But it doesn't select all the data and I'm not sure how to populate the formulas on the 'WO's (DSP Only)' tab also.

Any help would be greatly appreciated.

Thanks!

 

[nirvehex]

I have this code so far, but it doesn't two things still:

Sub WODSPTab()
'
' WODSPTab Macro
'


'Filter
 Worksheets("WOs").Range("A1").AutoFilter _
  Field:=16, Criteria1:="=DSP" _
        , Operator:=xlOr, Criteria2:="=REC"




'Paste Data from WO's Tab to WOs (DSP Only) tab


    Sheets("WOs").Select
    Range("A1").Select
    ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
    Selection.Copy
    Sheets("WOs (DSP Only)").Select
    Range("G1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        
'Paste Array Percentile Forumlas


Sheets("WOs (DSP Only)").Select


    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[11],""-"",RC[10],""-"",RC[25],""-"",RC[23])"


    Range("B2").Select
     Selection.FormulaArray = _
        "=PERCENTILE.INC(IF(R2C1:R2C1=RC[-1],R2C32:R2C32),0.5)"
        
        Range("C2").Select
     Selection.FormulaArray = _
        "=PERCENTILE.INC(IF(R2C1:R2C1=RC[-2],R2C32:R2C32),0.65)"
        
        Range("D2").Select
     Selection.FormulaArray = _
        "=PERCENTILE.INC(IF(R2C1:R2C1=RC[-3],R2C32:R2C32),0.75)"
        
        Range("E2").Select
     Selection.FormulaArray = _
        "=PERCENTILE.INC(IF(R2C1:R2C1=RC[-4],R2C32:R2C32),0.9)"
        
        Range("F2").Select
     Selection.FormulaArray = _
        "=PERCENTILE.INC(IF(R2C1:R2C1=RC[-5],R2C32:R2C32),0.99)"
    
        
End Sub

The ranges are locked and I'm not sure how to unlock them when calculating formulas.

The formulas in cells B2-F2 should be

{=PERCENTILE.INC(IF($A$2:last row=A2,$AF$2:last row),.5)}

.

Instead the code generates

{=PERCENTILE.INC(IF($A$2:$A$2=A2,$AF$2:$AF$2),.5)}

Any help please? I'm trying here...

Thank you!

 

[nirvehex]

I think I almost have this figured out, but my Range.Formulas are not working. Here is my code. Any help would be greatly appreciated:

Sub WODSPTab()
'
' WODSPTab Macro
'


Dim Lastrow As Long
Application.ScreenUpdating = False
Lastrow = Range("A" & Rows.Count).End(xlUp).Row


'Filter
 Worksheets("WOs").Range("A1").AutoFilter _
  Field:=16, Criteria1:="=DSP" _
        , Operator:=xlOr, Criteria2:="=REC"




'Paste Data from WO's Tab to WOs (DSP Only) tab


    Sheets("WOs").Select
    Range("A1").Select
    ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
    Selection.Copy
    Sheets("WOs (DSP Only)").Select
    Range("G1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        
'Paste Array Percentile Forumlas


Sheets("WOs (DSP Only)").Select


    Range("A2").Formula = "=CONCATENATE(L3," - ",K3," - ",Z3," - ",X3)"
    Selection.AutoFill Destination:=Range("A2:A & LastRow")


    Range("B2").Select
     ActiveCell.FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & Lastrow & "=A2,$AF$2:$A$" & Lastrow & "),.5)"
     Selection.AutoFill Destination:=Range("B2:B & LastRow")
        
        Range("C2").Select
     ActiveCell.FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & Lastrow & "=A2,$AF$2:$A$" & Lastrow & "),.65)"
     Selection.AutoFill Destination:=Range("C2:C & LastRow")
        
        Range("D2").Select
     ActiveCell.FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & Lastrow & "=A2,$AF$2:$A$" & Lastrow & "),.75)"
     Selection.AutoFill Destination:=Range("D2:D & LastRow")


        Range("E2").Select
     ActiveCell.FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & Lastrow & "=A2,$AF$2:$A$" & Lastrow & "),.9)"
     Selection.AutoFill Destination:=Range("E2:E & LastRow")
        
        Range("F2").Select
     ActiveCell.FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & Lastrow & "=A2,$AF$2:$A$" & Lastrow & "),.99)"
     Selection.AutoFill Destination:=Range("F2:F & LastRow")


    
        
End Sub

 

[nirvehex]

I figured it out!

Sheets("WOs (DSP Only)").Select
With Worksheets("WOs (DSP Only)")


    With .Range("A2:A" & Lastrow)
    .Formula = "=L2&""-""&K2&""-""&Z2&""-""&X2"
    End With
    
    With .Range("B2:B" & Lastrow)
    .FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & Lastrow & "=A2,$AF$2:$AF$" & Lastrow & "),.5)"
    End With
    
    
End With