excel - 如何使用 vba 将过滤后的值仅粘贴到受此类过滤器影响的单元格中？

Question

我目前正在研究一个宏，它当前根据值过滤表，然后在应用过滤器后复制列下的数据(让它工作)。但是，我不知道如何将这些值粘贴到同一个表中，从而覆盖不同列中可见单元格下的数据。正在复制以红色(图片)突出显示的值，现在我只需将它们粘贴到以黄色突出显示的单元格中。谢谢!

Public Sub DxcDateUpdate()
Application.ScreenUpdating = False
Dim Mwb As Workbook
Dim ws As Worksheet

Set Mwb = ThisWorkbook
Set ws = Mwb.Worksheets("Commission")
Set ws2 = Mwb.Worksheets("test")
        
        
lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
ws.Range("A1").AutoFilter Field:=31, Criteria1:="DXC/TPV.com Enrollment"
ws.Range("AG2:AG" & lr).SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range '''here is where idk what to do?'''
    
Application.ScreenUpdating = True
End Sub

Answer 1

您不能将不连续的范围粘贴为不连续的。您应该在每个范围单元格之间迭代并使用偏移量复制它，或者使用 c.row 将范围构建为粘贴.请尝试下一个改编的代码:

Sub DxcDateUpdate()
Dim Mwb As Workbook, ws As Worksheet, rngVis As Range, c As Range, LR As Long

Set Mwb = ThisWorkbook
Set ws = Mwb.Worksheets("Commission")
Set ws2 = Mwb.Worksheets("test")
        
        
LR = ws.cells(ws.rows.Count, 1).End(xlUp).row
ws.Range("A1").AutoFilter field:=31, Criteria1:="DXC/TPV.com Enrollment"
Set rngVis = ws.Range("AG2:AG" & LR).SpecialCells(xlCellTypeVisible) 
 For Each c In rngVis.cells
    c.Offset(0, -28).value = c.value
 Next
End Sub

当然，为了使代码更快，您应该使用一些优化行(ScreenUpdating = False，EnableEvents = False，Calculation = xlCalculationManual，后跟 True， True， xlCalculationAutomatic)。