I am trying to make a macro that will copy data from one book (indicating the path) to an open book (where there will be a button).

There is a code that gives the error 'Run-time error' 438 '- Object doesn't support this property or method' in the line Sht = WB.Sheets ("Sheet1")

How to win?

Sub объем() 'Читаем другой файл:' Dim WB As Workbook Set WB = Application.Workbooks.Open("C:\Users\S.Bochkarev\Desktop\ppweb2.xlsm") Sht = WB.Sheets("Sheet1") 'Обрабатываем диапазон' For Counter = 1 To 20 Set curCell = Sht.Cells(Counter, 3) 'curCell.Value - значение ячейки из другого файла вставляем куда надо' ActiveWorkbook.Sheets("Остатки").Cells(1, 1).Value = curCell.Value Next Counter WB.Close End Sub

    1 answer 1

    Assigning an object to a variable - Set operator

    The book you placed in a variable

     Set WB = Application.Workbooks.Open("C:\Users\S.Bochkarev\Desktop\ppweb2.xlsm")

    But the sheet is also an object, you need

     Set Sht = WB.Sheets("Sheet1")

    But the cell as an object is not needed; it is enough to enter the value in the variable:

     curCell = .Cells(Counter, 3).Value

    Application.Workbooks.Open - opened the book. Often the book after opening is recalculated (presence of names, UV, volatile formulas ...). When you close Excel will ask for confirmation of the action. Correct - disable messages:

     Application.DisplayAlerts = False WB.Close Application.DisplayAlerts = True

    After the macro completes, the variables are cleared, but there are cases when the memory remains occupied, so it is desirable to clean it forcibly

      Set WB = Nothing: Set Sht = Nothing End Sub

    It is not clear why in the cycle to fill one cell ( Sheets ("Leftovers"). Cells (1, 1) .Value ) 20 times ... It makes no sense.

    Option. Load objects into memory using With . The insertion cell is also changing (as needed — see the task)

     Sub объем() Dim sht As Worksheet Dim i As Long With Application: .ScreenUpdating = False: .DisplayAlerts = False: End With Set sht = TnisWorkbook.Sheets("Остатки") 'Открываем другой файл' With Application.Workbooks.Open("C:\Users\S.Bochkarev\Desktop\ppweb2.xlsm") With .Sheets("Sheet1") 'Обрабатываем диапазон' For i = 1 To 20 'значение ячейки из другого файла вставляем куда надо' sht.Cells(i, 1).Value = .Cells(i, 3).Value Next i End With .Close End With With Application: .ScreenUpdating = True: .DisplayAlerts = True: End With Set sht = Nothing End Sub
    • Thanks, now here is ActiveWorkbook.Sheets ("Remains"). Curses Cells (3, 3). Value = curCell. Value - subscribe out of range - Stepan
    • subscribe out of range - out of range. Apparently, the variable is not filled. See the code in the answer. - vikttur
    • on the line With Application.ScreenUpdating = False: .DisplayAlerts = False: End With the error "with object must be user-defined type". Without these lines works. For what they understood, but how to fix did not understand) - Stepan
    • This is obtained by inserting values ​​starting with A3 in the column, but how to set the number of columns? - Stepan
    • With Application: line breaks were omitted (colon). I did not understand the number of pillars. If you need to fill another column, add another row in the loop, for example: sht.Cells(i, 2).Value = .Cells(i, 5).Value - vikttur