1

I have an auto filter on my excel file activated in my macro. When I run my code, I got the following error message:

Run time error 438, Object does not support this property or method

I tried to run my macro step by step via F8, it seems that this line below generated this error:

Range(ActiveCell, ActiveCell.End(xlDown)).Paste

I want that my macro execute the following action:

  • All my visible filtered lines in column AA are copied in column K
  • All my visible filtered lines in column J are replaced by the value “J0”

If someone could help me, it would be great.

Sub fuelstep4ArvalBIF()
  Application.ScreenUpdating = False
  Range("A1").Select
  Selection.AutoFilter

  ActiveSheet.Range("$A$1:$AF$3000").AutoFilter Field:=25, Criteria1:=Array( _
  "21.00", "21", "19.00", "19", "5.50", "5.5", "13.00", "13"), Operator:=xlFilterValues

  Call  FirstVisibleCellAA
  Range(ActiveCell, ActiveCell.End(xlDown)).Copy  Call FirstVisibleCellK
  Range(ActiveCell, ActiveCell.End(xlDown)).Paste Call FirstVisibleCellJ 
  ActiveCell.Value = "J0" ActiveCell.Copy Range(ActiveCell, 
  ActiveCell.End(xlDown)).Paste 
End Sub 

Sub FirstVisibleCellK()
  With ActiveSheet.AutoFilter.Range
   Range("K" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
  End With
End Sub 

Sub FirstVisibleCellAA()
  With ActiveSheet.AutoFilter.Range Range( _ 
  "AA" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
  End With
End Sub 

Sub FirstVisibleCellJ() With ActiveSheet.AutoFilter.Range
  Range("J" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With End Sub

Thanks in advance for your help.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Xavi
  • 207
  • 1
  • 3
  • 20
  • @Rawrplus, excuse me for the unintended edit after yours (while you did a much better job too). OT: You might want to start by avoiding the use of `.Select`, `ActiveSheet` and `ActiveCell` and declare variables instead. Check [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) thread for some nice explaination. – JvdV Aug 27 '18 at 11:40
  • @JvdV I believe there should be an option to rollback, but oh well, it's fine. :) – Samuel Hulla Aug 27 '18 at 11:42
  • I've had issues pasting to a range if the copy range contains a different number of cells. Generally, you paste to a single cell, and the data you paste will start in that cell and use as much space as it needs. – Steve Bull Aug 27 '18 at 14:12
  • Thanks a lot but unfortunately I do not manage to do what I want yet… What I want to do is to copy and paste the datas from my columns AA to column K (only the visible cells of my activesheet after applying the filter according to my code below) When I run my macro I got the following error message below on my line "Range(ActiveCell, ActiveCell.End(xlDown)).Copy WS.r": "compile error, Method or data member not found" Here please find my code below: – Xavi Aug 29 '18 at 12:26
  • Sub fuelstep4ArvalBIF() Dim WS As Worksheet Dim r As Range Set WS = ActiveSheet Set r = Range("K:K").SpecialCells(xlCellTypeVisible) Application.ScreenUpdating = False Range("A1").Select Selection.AutoFilter WS.Range("$A$1:$AF$3000").AutoFilter Field:=25, Criteria1:=Array( _ "21.00", "21", "19.00", "19", "5.50", "5.5", "13.00", "13"), Operator:=xlFilterValues Call FirstVisibleCellAA Range(ActiveCell, ActiveCell.End(xlDown)).Copy WS.r End Sub – Xavi Aug 29 '18 at 12:28
  • Sub FirstVisibleCellAA() With ActiveSheet.AutoFilter.Range Range( _ "AA" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select End With End Sub – Xavi Aug 29 '18 at 12:28
  • I finally managed to solve my problem with myself with this Code, I am very happy... – Xavi Aug 29 '18 at 14:11
  • I finally managed to solve my problem by myself with this Code below:ub fuelstep4ArvalBIF() ' ' fuelstep2ALDBIF ' ' Dim r1, r2, WS, myMultipleRange As Range Set WS = ActiveSheet Set r1 = Range("AA:AA").SpecialCells(xlCellTypeVisible) Set r2 = Range("K:K").SpecialCells(xlCellTypeVisible) Set myMultipleRange = Union(r1, r2) Application.ScreenUpdating = False Range("A1").Select Selection.AutoFilter – Xavi Aug 29 '18 at 14:13
  • ActiveSheet.Range("$A$1:$AF$3000").AutoFilter Field:=25, Criteria1:=Array( _ "21.00", "21", "19.00", "19", "5.50", "5.5", "13.00", "13"), Operator:=xlFilterValues myMultipleRange.FillLeft End sub – Xavi Aug 29 '18 at 14:13

0 Answers0