Opening another excel file and copy the “specific” filtered columns to the current active workbook.

huangapple go评论56阅读模式
英文:

Opening another excel file and copy the "specific" filtered columns to the current active workbook

问题

我正在尝试打开另一个具有筛选列的工作簿,但每次运行宏时,它只复制一个可见单元格到我的活动工作簿。

以下是我使用的代码:

Sub SelectAfile()
'选择文件宏
    Dim FileLocation As String
    Dim LastRow As Long, wsPaste As Worksheet, curr_lrow As Long
    Dim wb As Workbook, ImportWorkbook As Workbook, wsImport As Worksheet
    
    '打开文件
    FileLocation = Application.GetOpenFilename
    If FileLocation = "False" Then
        MsgBox "请选择一个文件。", vbCritical
        Exit Sub
    End If
    
    '设置用于复制和目标工作表的变量
    Set wb = ActiveWorkbook         '复制
    Set wsPaste = wb.Worksheets(1)  '粘贴
    
    Application.ScreenUpdating = False
    Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)
    Set wsImport = ImportWorkbook.Worksheets(2)
    
    '1. 根据列A中的数据查找复制范围中的最后一个已使用行
    LastRow = wsImport.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
    '2. 根据列A中的数据查找目标范围中的第一个空白行
    curr_lrow = wsPaste.Cells(Rows.Count, "A").End(xlUp).Row + 1 '比.End(xlDown)更安全...
    
    CopyValues wsImport.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible), wsPaste.Range("A" & curr_lrow)
    CopyValues wsImport.Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible), wsPaste.Range("B" & curr_lrow)
    CopyValues wsImport.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible), wsPaste.Range("C" & curr_lrow)
    
End Sub

Sub CopyValues(rngFrom As Range, rngTo As Range) '继续自SelectAfile
    With rngFrom
        rngTo.Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub

我尝试了一些其他代码,比如循环遍历每个可见单元格,但当数据量很大时,这需要太长时间。

英文:

Im trying to open another workbook with a filtered columns but every time I run the macro, it only copies 1 visible cell only to my active workbook.

here's the code that I use

Sub SelectAfile()
'Select a file Macro
    Dim FileLocation As String
    Dim LastRow As Long, wsPaste As Worksheet, curr_lrow As Long
    Dim wb As Workbook, ImportWorkbook As Workbook, wsImport As Worksheet
    
    'Open File
    FileLocation = Application.GetOpenFilename
    If FileLocation = "False" Then
        MsgBox "Please select a file.", vbCritical
        Exit Sub
    End If
    
    'Set variables for copy and destination sheets
    Set wb = ActiveWorkbook         'Copy
    Set wsPaste = wb.Worksheets(1)  'Paste
    
    Application.ScreenUpdating = False
    Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)
    Set wsImport = ImportWorkbook.Worksheets(2)
    
    '1. Find last used row in the copy range based on data in column A
    LastRow = wsImport.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
    '2. Find first blank row in the destination range based on data in column A 
    curr_lrow = wsPaste.Cells(Rows.Count, "A").End(xlUp).Row + 1 'safer than .End(xlDown)...
    
    CopyValues wsImport.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible), wsPaste.Range("A" & curr_lrow)
    CopyValues wsImport.Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible), wsPaste.Range("b" & curr_lrow)
    CopyValues wsImport.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible), wsPaste.Range("c" & curr_lrow)
    
End Sub

Sub CopyValues(rngFrom As Range, rngTo As Range) 'Continuation from SelectAfile
    With rngFrom
        rngTo.Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub

I tried some other codes like looping through each visible cells but it takes too long when the data is big

答案1

得分: 0

如果在CopyValues函数中设置断点并检查rngFrom.Address,您会注意到它是一个以逗号分隔的“列表”而不仅仅是一个单一范围。
您需要单独处理所有这些区域,例如以下代码可能适用于您

Sub CopyValues(rngFrom As Range, rngTo As Range) '从SelectAfile继续
    Dim x As Variant
    With rngFrom
        For Each x In rngFrom.Areas
            rngTo.Resize(x.Rows.Count, x.Columns.Count).Value = x.Value
            Set rngTo = rngTo.Offset(x.Rows.Count, 0)
        Next
    End With
End Sub
英文:

If you place a breakpoint in your CopyValues function and examine rngFrom.Address you will notice that it is a comma-separated "list" of ranges, not just a single range.
You need to deal with all of those Areas individually e.g. something like this might work for you

Sub CopyValues(rngFrom As Range, rngTo As Range) 'Continuation from SelectAfile
    Dim x As Variant
    With rngFrom
        For Each x In rngFrom.Areas
            rngTo.Resize(x.Rows.Count, x.Columns.Count).Value = x.Value
            **Set rngTo = rngTo.Offset(x.Rows.Count, 0)**
        Next
    End With
End Sub

huangapple
  • 本文由 发表于 2023年3月8日 16:54:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/75671026.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定