嘗試遍歷作業表以在日期上應用過濾器，并將所有過濾后的資料復制到“報告”表中。
這是代碼，它只回圈第一張表（美元）而不是第二張表（歐元）。
Sub SheetLoop()
Dim Ws As Worksheet
Dim wb As Workbook
Dim DestSh As Worksheet
Dim Rng As Range
Dim CRng As Range
Dim DRng As Range
Set wb = ThisWorkbook
Set DestSh = wb.Worksheets("Report")
Set CRng = DestSh.Range("L1").CurrentRegion
Set DRng = DestSh.Range("A3")
For Each Ws In wb.Worksheets
If Ws.Name <> DestSh.Name Then
Set Rng = Ws.Range("A1").CurrentRegion
Rng.AdvancedFilter xlFilterCopy, CRng, DRng
End If
Next Ws
End Sub
uj5u.com熱心網友回復：
鎖定21小時。對此答案的評論已被禁用，但它仍在接受其他互動。了解更多。由于AdvancedFilter需要過濾范圍標題，因此您不能僅復制過濾范圍的一部分，但您可以洗掉復制范圍的第一行，除了第一個復制范圍（來自第一張紙）：
Sub SheetLoop()
Dim Ws As Worksheet, wb As Workbook, DestSh As Worksheet
Dim Rng As Range, CRng As Range, DRng As Range, i As Long
Set wb = ThisWorkbook
Set DestSh = wb.Worksheets("Report")
Set CRng = DestSh.Range("L1").CurrentRegion
Set DRng = DestSh.Range("A3")
For Each Ws In wb.Worksheets
If Ws.name <> DestSh.name Then
i = i 1
Set Rng = Ws.Range("A1").CurrentRegion
Rng.AdvancedFilter xlFilterCopy, CRng, DRng
If i > 1 Then DRng.cells(1).EntireRow.Delete xlUp 'delete the first row of the copied range, except the first case
Set DRng = DestSh.Range("A" & DestSh.rows.count).End(xlUp).Offset(1) 'reset the range where copying to
End If
Next Ws
MsgBox "Ready..."
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/526518.html
標籤：vbafor循环
下一篇：如何優化for回圈？[復制]