Копировать все данные из одной книги в другую с помощью 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.