Select range in another file after opening it

Question

Can you help me please with the following problem? I have a code which opens an excel file in a certain locations and makes a filter in it, but after this I can't select the range I need:

Private Sub CommandButton1_Click()

'dim Wb as string
'dim Newdata as string

    Newdata = "M:\Finance\REPORTING\2022_08\Hóközi FC\GL.xlsx"
    Set Wb = Workbooks.Open(Newdata)
    ThisWorkbook.RefreshAll
    ActiveSheet.Range("A:AE").AutoFilter Field:=30, Criteria1:="P2"
    Windows("GL.xlsx").Activate
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
End Sub

Thanks in advance

As a good practice, it is best to avoid `Select` and `Activate` in VBA. You might find [this post](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) useful. Also, when referring to `Range` you should use `Wb.Range` or `ThisWorkbook.Range` to avoid any confusion. — Thomas C., Aug 18 '22 at 06:39

VBasic2008 · Accepted Answer · 2023-02-01T21:13:51.987

Copy a Filtered Range

Option Explicit

Private Sub CommandButton1_Click()

    Const Newdata As String = "M:\Finance\REPORTING\2022_08\Hóközi FC\GL.xlsx"
    
    ThisWorkbook.RefreshAll ' irrelevant???
    
    ' Open and reference the source workbook ('swb').
    Dim swb As Workbook: Set swb = Workbooks.Open(Newdata)
    ' Reference the source worksheet ('sws').
    Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1") ' adjust!
    ' Turn off AutoFilter.
    If sws.AutoFilterMode Then sws.AutoFilterMode = False
    ' Reference the source (table) range ('srg').
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    
    srg.AutoFilter Field:=30, Criteria1:="P2"
    
    srg.SpecialCells(xlCellTypeVisible).Copy
    
    ' You could e.g. do...
    'srg.SpecialCells(xlCellTypeVisible).Copy _
        ThisWorkbook.Worksheets("Sheet1").Range("A1")
    ' ... if the workbook containing this code ('ThisWorkbook')
    ' is the destination workbook.

End Sub

Select range in another file after opening it

1 Answers1

Copy a Filtered Range