Help required with Autofill after copy paste

[Mitchx]

Hi Guys,

I have the code below:

The last part doesn't work - it gives an error 1004 Method Autofill of Range class failed.
Basically what I want: There are formulas present in cell D2 up until O2. I want to drag this formula to the newly copied cells after the copy paste of the data mentioned in the macro.

The formulas in D2 till O2 will always remain.

Sub Copy_Paste()



Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, lRow As Long

Set ws1 = ThisWorkbook.Sheets("Origineel")
Set ws2 = ThisWorkbook.Sheets("Origineel (selectie)")
Set ws3 = ThisWorkbook.Sheets("Origineel (selectie 0-1)")

lRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
lRow3 = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Row

    With ws1
        .Range("A2").AutoFilter Field:=1 'set your filter

        'copy the visible cells in each column from row 2 and resize to the last row
        'paste to the the cell you want your copied range to start in your second worksheet
        .Range("A2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("A2")
        .Range("B2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("B2")
        .Range("C2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("C2")
        .Range("F2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("D2")
        .Range("H2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("E2")
        .Range("J2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("F2")
        .Range("L2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("G2")
        .Range("M2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("H2")
        .Range("Q2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("I2")
        .Range("T2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("J2")
        .Range("U2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("K2")
        .Range("V2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("L2")
        .Range("AB2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("M2")
        .Range("AF2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("N2")
        .Range("AJ2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("O2")
        
        
        .Range("A1").AutoFilter 'clear the filter
    End With
    
With ws2
        .Range("A2").AutoFilter Field:=1 'set your filter
        .Range("A2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws3.Range("A2")
        .Range("B2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws3.Range("B2")
        .Range("C2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws3.Range("C2")
        .Range("A1").AutoFilter

   End With
   
With ws2
   Range("D2:O2").Select
   Selection.AutoFill Destination:=Range("D2:O" & lRow3)


   End With
   
   
    End Sub

If you require more information feel free to reply.

 

[cmowla]

I can't be for certain that this is the cause, but let's go for the easiest thing first:

Here, you have a With statement as you have several before. But you have no . in front of Range here. (Neither one, when they both should have one.) Is this last bit supposed to be done in the active sheet? Because that's what it's trying to do.

With ws2
   Range("D2:O2").Select
   Selection.AutoFill Destination:=Range("D2:O" & lRow3)