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

user_18364452 0
Viewed 48

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
paste runtime-error vba excel
2 Answers

Try this:

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

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
user_98140690
Related