英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论