Help required with Autofill after copy paste

Mitchx

New Member
Joined
Oct 20, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

cmowla

Well-known Member
Joined
Sep 21, 2021
Messages
536
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
With ws2
   Range("D2:O2").Select
   Selection.AutoFill Destination:=Range("D2:O" & lRow3)
 

Forum statistics

Threads
1,181,974
Messages
5,933,031
Members
436,872
Latest member
Nickytoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top