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