Default Macro using Advanced Filter with Relative Reference

[Marco73]

Hello,

I am looking for a VBA sentence which make possible to copy a full data base which can change (Relative Reference) using the advance filter:

Sub MacroTest()
'
' MacroTest Macro
'
'
Workbooks("Other Excel file containing the Data Base.xlsx").Sheets("Data").Range("A3:EN3897"). _
AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"'Reference Sheet!Extract"), Unique:=False
Windows("Other Excel file containing the Data Base.xlsx").Activate
Windows("Reference Sheet.xlsx").Activate
End Sub

How can I change that: ("A3:EN3897"). to a relative reference type?

Thnak you for your help,
Marc

 

[Marco73]

PS: Relative reference type:
Range("A3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Thanks,
Marc

 

[JackDanIce]

Welcome to the board, try:

Sub MacroTest_v1

Dim LastRow as Long

With Workbooks("Other Excel file containing the Data Base.xlsx")
  With .Sheets("Data")
      LastRow = .Range("A" & Rows.Count).End(xlup).Row
      .Range("A3:EN" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Reference Sheet!Extract"), _
         Unique:=False
  End With
End With

End Sub