Копировать все данные из одной книги в другую с помощью vba

При запуске следующего кода появляется сообщение об ошибке "object variable или with block variable not set". Что не так с кодом?

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
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="" string="" sheet="" worksheet="" integer,="" j="" integer="" mainworkbook="" workbook="" x="" workbook,="" y="" ws1="" worksheet,="" ws2="" application.screenupdating="False" &="" "*.xl??")="" set="" filename)="" windows("book3.xlsm").activate="" ' <<<<<<<<<<<<<="" '<<not="" sure="" why="" you="" have="" close="" lines="" within="" with="" block="" .cells.copy="" ws2.cells="" end="" y.close="" true="" x.close="" false="" <="" code=""> </i>

licensed under cc by-sa 3.0 with attribution.