I have lots of problems opening a remote workbook and copying parts of a sheet, I have tried three versions but nothing works reliably:
Sub pfdbPlainImport()
With Application
.Calculation = xlManual
.DisplayAlerts = False
.RTD.ThrottleInterval = 100000 'slow down bloomberg real time data
End With
....
Dim WB As Workbook
Set WB = Workbooks.Open("X:\40-PF\PFDB\PFDB_today\PM.xls")
Dim WS As Worksheet
Set WS = WB.Sheets("PM_data_short_SAPNbr")
'copy code version 1: (fails occasionally on paste with run time error)
WS.Activate
WS.Range("A2").Resize(10000, 42).Select
Selection.copy
pfdb.Activate
pfdb.Range("A2").Resize(10000, 42).Select
pfdb.Paste
WB.Close (False)
'copy code version 2: (fails occasionally with run time error)
WS.Range("A2").Resize(10000, 42).copy
pfdb.Range("A2").Resize(10000, 42).PasteSpecial Paste:=xlValues
'copy code version 3: (fails occasionally with run time error)
WS.Range("A2").Resize(10000, 42).copy _
Destination:=pfdb.Range("A2").Resize(10000, 42)
I need to copy only the values. is there a better way of doing this.
Thanks.
Bookmarks