excel vba宏：将列信息复制到另一个工作簿

Question

我正在使用此宏将数据从工作簿'x'中的3列复制到工作簿'y'，而不复制隐藏行。

Sub GetDataDemo() 
Const FileName As String = "EHS.xlsx" 
Const SheetName As String = "PO" 
FilePath = "C:\Users\DD\Desktop\" 
Dim wb As Workbook 
Dim this As Worksheet 
Dim i As Long, ii As Long 

Application.ScreenUpdating = False 

If IsEmpty(Dir(FilePath & FileName)) Then 

    MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist" 
Else 

    Set this = ActiveSheet 

    Set wb = Workbooks.Open(FilePath & FileName) 

    With wb.Worksheets(SheetName).Range("Y:AA") 

     ii = 3 
     For i = 3 To 500 

      If Not .Rows(i).Hidden Then 

       .Cells(i).Copy 
       this.Range("P:R").Cells(ii).Paste 
       ii = ii + 1 
      End If 
     Next i 
    End With 
End If 

ActiveWindow.ScreenUpdating = True 
End Sub 

我不断收到自动化错误（错误440）附近的行：

this.Range("P:R").Cells(ii).Paste 

感谢您的帮助提前！

Answer 1

您应该可以复制&批量粘贴可见的单元格/行。

With wb.Worksheets(SheetName).Range("Y3:AA500") 
    on error resume next 
    .SpecialCells(xlcelltypevisible).Copy this.Range("P3") 
    on error goto 0 
End With 

。粘贴是工作表的成员，而不是范围或单元格的成员。

this是保留名称;将保留名称重用为变量不被认为是“最佳做法”。