Import worksheet error

Posted on 2011-10-03
Last Modified: 2012-05-12
I am using the following code to add a new worksheet to the workbook. When I expect the new sheet to be added to the end of the workbook and named "Import" , it is actually renaming the last sheet to "import"  and is importing sheet1 without renaming it. Please help!

Public Sub ImportWksheet()
'On Error GoTo ErrHandler

  Dim DstWkb        As Workbook
  Dim FileFilter    As String
  Dim Filename      As String
  Dim SrcWkb        As Workbook
  Dim srcWks        As String
  Dim dstWks        As String
    srcWks = "Sheet1"         'Name of the Worksheet to be copied
    dstWks = "Import"    'Name the copied Worksheet will be given
    FileFilter = "Excel Files, *.xls;*.xla;*.csv, All Files, *.*"
    Filename = Application.GetOpenFilename(FileFilter, 1)
    If Filename = "False" Then Exit Sub
      Set DstWkb = ThisWorkbook
      Set SrcWkb = Workbooks.Open(Filename:=Filename, ReadOnly:=True)
      Application.ScreenUpdating = False
        'On Error Resume Next
          With SrcWkb.Worksheets(srcWks)
            If Err.Number = 9 Then
              MsgBox srcWks & " was not found in " & SrcWkb.Name
              GoTo OrderlyExit
            End If
            'On Error GoTo 0
            .Copy After:=DstWkb.Worksheets(DstWkb.Worksheets.Count)
            'On Error Resume Next
              ActiveSheet.Name = dstWks
              If Err.Number = 1004 Then
                MsgBox dstWks & " already exists in " & DstWkb.Name & vbCrLf _
                     & "Two worksheets can not have the same name."
                GoTo OrderlyExit
              End If
            'On Error GoTo 0
          End With
      Application.ScreenUpdating = True
      SrcWkb.Close SaveChanges:=False
      ysnValid = True
       ' MsgBox "Failed to Import to database.  Check database is not read-only."
        'Exit Sub
End Sub

Open in new window

Question by:smm6809
    LVL 33

    Accepted Solution

    Instead of using ActiveSheet use an explicit reference to the workbook you want to rename.

    DstWkb.Worksheets(DstWkb.Worksheets.Count).Name = "Import"
    LVL 33

    Expert Comment

    Oops, meant worksheet you want to rename.

    Author Comment

    Error message:
    Copy method of worksheet class failed.+
    LVL 33

    Expert Comment

    The code posted was to replace the ActiveSheet.Name, nothing to do with the Copy.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now