Hi,
I have written the code below to copy a range from a saved workbook and to paste it into the active workbook that the project is saved in:
Sub Import_stats()
Dim Wb1 As Workbook
Application.ScreenUpdating = False
Set Wb1 = Workbooks.Open("S:\Head Office\! Advocacy and Communications\! Enquiries\Organisational Management\Review And Reporting\Reports & Statistics\Combined stats workbook\Weekly\Stats import files\statstest.xlsx") 'change this as u need
Wb1.Sheets(1).Range("K1:K10").Copy
Wb1.Close SaveChanges:=False
Sheets("Mon").Select
Range("D6").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
It works perfectly but I need to select another range from the same saved workbook so I re-wrote the code as follows:
Sub Import_stats()
Dim Wb1 As Workbook
Application.ScreenUpdating = False
Set Wb1 = Workbooks.Open("S:\Head Office\! Advocacy and Communications\! Enquiries\Organisational Management\Review And Reporting\Reports & Statistics\Combined stats workbook\Weekly\Stats import files\statstest.xlsx") 'change this as u need
Wb1.Sheets(1).Range("K1:K10").Copy
Wb1.Close SaveChanges:=False
Sheets("Mon").Select
Range("D6").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Wb1.Sheets(1).Range("L1:L10").Copy
Wb1.Close SaveChanges:=False
Sheets("Mon").Select
Range("E6").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
When I run this code I get the error message:
Run-time error '-2147221080 (80040198)':
Method 'sheets' of object '_workbook' failed
The line "Wb1.Sheets(1).Range("L1:L10").Copy' is highlighted in yellow when I go to de-bug.
Can anyone tell me where I am going wrong please?
Thanks very much in advance
Michael
Bookmarks