+ Reply to Thread
Results 1 to 3 of 3

Copy sheet from another workbook - runtime error 1004

Hybrid View

  1. #1
    Registered User
    Join Date
    05-13-2010
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2003
    Posts
    3

    Copy sheet from another workbook - runtime error 1004

    Hi,
    I am trying to copy a whole sheet from a workbook into another. My current code is presented below. When I run this I get 'Runtime error '1004'. The copy method in the Worksheet class failed.' Any ideas or suggestions?
    Cheers

            Dim basebook As Workbook
        Dim oubook As Workbook
        
        Set basebook = ThisWorkbook
        Set oubook = Workbooks.Open(Application.GetOpenFilename("Excel file,*.xls", , "Open Occasional Use log file..."))
        
        oubook.Worksheets(1).Copy after:= _
        basebook.Sheets(basebook.Sheets.Count)
        ActiveSheet.Name = oubook.Name
        oubook.Close

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: Copy sheet from another workbook - runtime error 1004

    work for me
    possible the workbook sheet name is the same as an existing one?
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Copy sheet from another workbook - runtime error 1004

    I'd prefer:

    Sub tst6()
      With Workbooks.Open(Application.GetOpenFilename("Excel file,*.xls", , "Open Occasional Use log file..."))
         .sheets(1).Copy ,Thisworkbook.Sheets(Thisworkbook.Sheets.Count)
         .sheets(sheets.count).Name = "Q_" & Thisworkbook.Name
         .Close True
      End With
    End Sub
    You'd better not give the same name to a sheet and a workbook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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