Results 1 to 5 of 5

Problem with vb code when copying and pasting

Threaded View

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Problem with vb code when copying and pasting

    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
    Last edited by Richard Buttrey; 07-04-2012 at 03:44 PM. Reason: code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1