使用vba将整个数据从一个工作簿复制到另一个

Question

运行以下代码时，出现“对象变量或块变量未设置”错误消息。代码有什么问题？

 Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As  Integer 
     Dim mainworkBook As Workbook 

     Application.ScreenUpdating = False 
     directory = "C:\Users\425410\Desktop\MYExcel\" 
     fileName = Dir(directory & "*.xl??") 
    Dim x As Workbook, y As Workbook 
    Dim ws1 As Worksheet, ws2 As Worksheet 

    Set x = Workbooks.Open(directory & fileName) 
    Windows("Book3.xlsm").Activate 

    Set ws1 = x.Sheets(1) 
    Set ws2 = y.Sheets(1) 

    With ws1 
     .Cells.Copy ws2.Cells 
      y.Close True 
      x.Close False 
    End With 

Answer 1

由于@ user3514930评论说您需要将y设置为工作簿对象。

增加了一些额外的代码编辑。

'<<I think grouping similar types together when declaring variables is clearer than OP 
'<<Also I prefer all declarations at the start of subroutines 

Dim directory As String, fileName As String 
Dim sheet As Worksheet 
Dim i As Integer, j As Integer 
Dim mainworkBook As Workbook 
Dim x As Workbook, y As Workbook 
Dim ws1 As Worksheet, ws2 As Worksheet 

Application.ScreenUpdating = False 
directory = "C:\Users\425410\Desktop\MYExcel\" 
fileName = Dir(directory & "*.xl??") 

Set x = Workbooks.Open(directory & fileName) 
Windows("Book3.xlsm").Activate 

Set y = Workbooks("someopenBook") '<<<<<<<<<<<<< 

Set ws1 = x.Sheets(1) 
Set ws2 = y.Sheets(1) 

'<<not sure why you have the workbook close lines within the With block 
With ws1 
    .Cells.Copy ws2.Cells 
End With 

y.Close True 
x.Close False