excel 打开另一个文件后选择其中的范围

wyyhbhjk 于 2023-02-05 发布在其他

关注(0)|答案(1)|浏览(91)

你能帮我解决下面的问题吗？我有一个代码，它可以在某个位置打开Excel文件，并在其中进行筛选，但在此之后，我不能选择我需要的范围：

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

先谢了

excel

来源：https://stackoverflow.com/questions/73398041/select-range-in-another-file-after-opening-it

1条答案

按热度按时间

e0uiprwp1#

复制过滤范围

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

赞(0）回复(0）举报 2023-02-05

我来回答

excel 打开另一个文件后选择其中的范围

1条答案

复制过滤范围

相关问题

热门标签

最新问答