Import worksheet error

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
       
OrderlyExit:
      Application.ScreenUpdating = True
      SrcWkb.Close SaveChanges:=False
      ysnValid = True
ErrHandler:
    
       ' MsgBox "Failed to Import to database.  Check database is not read-only."
        'Exit Sub
     
End Sub

Open in new window

smm6809Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

DstWkb.Worksheets(DstWkb.Worksheets.Count).Name = "Import"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieVBA ExpertCommented:
Oops, meant worksheet you want to rename.
0
smm6809Author Commented:
Error message:
Copy method of worksheet class failed.+
0
NorieVBA ExpertCommented:
The code posted was to replace the ActiveSheet.Name, nothing to do with the Copy.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.