Home > Software engineering >  I am new to VBA and could use some help. code suddenly stopped working Run-Time Error "1004&quo
I am new to VBA and could use some help. code suddenly stopped working Run-Time Error "1004&quo

Time:03-04

my code suddenly stopped working and I can't figure out why. Please help

    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:

Try this:

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

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