Code suddenly stopped working Run-Time Error "1004" PasteSpecial method of Range class fai-CodePudding

My code suddenly stopped working and I can't figure out why. I am new to VBA.

Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="ExcelFiles(*xls*),*xls*")
If FileToOpen <> False Then
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    OpenBook.Sheets(1).Range("A1:S250").Copy
ThisWorkbook.Worksheets("RecognitionsLog").Range("A2").PasteSpecial xlPasteValues
OpenBook.Close False


End If
Application.ScreenUpdating = True
End Sub

CodePudding user response：

Copy Values From a Closed Workbook

Your code works fine on my end but I prefer to copy values by assignment. Give it a try and let us know if the issue persists.

Option Explicit

Sub CopyValues()
    
    Application.ScreenUpdating = False
    
    Dim FileToOpen As Variant
    FileToOpen = Application.GetOpenFilename( _
        Title:="Browse for your File & Import Range", _
        FileFilter:="ExcelFiles(*.xls*),*.xls*")
    
    If FileToOpen <> False Then
        Dim OpenBook As Workbook: Set OpenBook = Workbooks.Open(FileToOpen)
        Dim srg As Range: Set srg = OpenBook.Worksheets(1).Range("A1:S250")
        Dim drg As Range: Set drg = ThisWorkbook.Worksheets("RecognitionsLog") _
            .Range("A2").Resize(srg.Rows.Count, srg.Columns.Count)
        drg.Value = srg.Value
        OpenBook.Close False
    End If
    
    Application.ScreenUpdating = True

End Sub

CodePudding user response：

Try this:

Set destinationRange = ThisWorkbook.Worksheets("RecognitionsLog").Range("A2")
destinationRange.PasteSpecial Paste:=xlPasteValues