VBA issue with getting file name

[jckhnln]

Hey there! I'm having some issues with this code below. It's ultimate goal is to Allow a user to select a file to use as a destination file, "wkbDestination" where a tab will be added into. And also allows a user to select a file to use as the source file, "wksSource" where the tab will be copied from. The code should then be making sure the active and destination aren't the same file then copy the tab of info I want into the destination file and delete the old one. The main issue I'm running into is the portion of code trying to allow the user to select a source file. Earlier in my code I have a piece that delimits a .txt to .xlsx and it stays open once completed. So initially I was thinking that it would be the active workbook and I could just set wksSource to the active workbook, but in the workflow right above it, when a user selects a workbook to use as the destination, that is made the active workbook. Disclaimer that I'm not super experienced with VBA so if there's a better way to do it that is fine too. Just keep getting mismatch errors or subscript out of range errors and not sure what's up. If this isn't clear enough let me know and I can try to explain better, thank you!

Sub CopyReplaceWorksheet()


 Dim lSheetIndex As Long
 Dim sErrMsg As String
 Dim wkbDestination As Workbook
 Dim wksSource As Worksheet, wksTemp As Worksheet
 
 On Error GoTo ErrProc
 Application.EnableCancelKey = xlErrorHandler
 Application.EnableEvents = False
 

FileToPasteIn = Application.GetOpenFilename(FileFilter:="Excel Workbooks(*.xls*),*.xls*", Title:="Open Rec File")
    If FileToPasteIn <> False Then
        Workbooks.Open FileToPasteIn
        End If
    Dim GetBook As String
    GetBook = ActiveWorkbook.Name
  
    Set wkbDestination = Workbooks(GetBook)
    
Dim FileForActive As Variant
Dim OpenBook As Workbook
FileForActive = Application.GetOpenFilename
If FileForActive <> False Then
    Set OpenBook = Application.Workbooks.Open(FileForActive)
    End If

 Set wksSource = Workbooks(OpenBook)
 If ActiveWorkbook.Name = wkbDestination.Name Then
   MsgBox "This macro won't copy Active Sheet in destination workbook."
   GoTo ExitProc
 End If
 
 lSheetIndex = lGetSheetIndex(sSheetName:=wksSource.Name, wkb:=wkbDestination)

 
 If lSheetIndex Then
   If lSheetIndex = wkbDestination.Sheets.Count Then
      Set wksTemp = wkbDestination.Worksheets.Add( _
         After:=wkbDestination.Sheets(lSheetIndex))
   End If
   
   Application.DisplayAlerts = False
   wkbDestination.Sheets(wksSource.Name).Delete
   Application.DisplayAlerts = True
 Else

   lSheetIndex = 1
 End If
   
 wksSource.Copy Before:=wkbDestination.Sheets(lSheetIndex)
 
ExitProc:
 On Error Resume Next
 
 If Not wksTemp Is Nothing Then
   Application.DisplayAlerts = False
   wkbDestination.Sheets(wksTemp.Name).Delete
   Application.DisplayAlerts = True
 End If
 
 Application.EnableEvents = True
 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc

End Sub[CODE=vba][CODE=vba]

[/CODE][/CODE]

 

[John_w]

See if this works for you, however you haven't said which sheet should be copied from the source workbook to the destination workbook and where it should be placed in the destination workbook. This macro copies the first source sheet to become the first sheet in the destination.

Public Sub Copy_Sheet_From_Source_To_Destination()

    Dim destFile As Variant, sourceFile As Variant
    Dim destWorkbook As Workbook, sourceWorkbook As Workbook
    Dim ws As Worksheet, numVisible As Long
    
    destFile = Application.GetOpenFilename(FileFilter:="Excel Workbooks(*.xls*),*.xls*", Title:="Select destination workbook")
    If destFile = False Then Exit Sub
    
    sourceFile = Application.GetOpenFilename(FileFilter:="Excel Workbooks(*.xls*),*.xls*", Title:="Select source workbook")
    If sourceFile = False Then Exit Sub
    
    If LCase(destFile) = LCase(sourceFile) Then
        MsgBox "The destination and source workbooks must be different files"
        Exit Sub
    End If
    
    Set sourceWorkbook = Workbooks.Open(sourceFile)
    Set destWorkbook = Workbooks.Open(destFile)
    
    If destWorkbook Is Nothing Then
        sourceWorkbook.Close SaveChanges:=False
        MsgBox "The destination and source workbooks must be different files"
        Exit Sub
    End If
    
    'Copy first worksheet in source workbook to first worksheet in destination workbook

    With destWorkbook
        sourceWorkbook.Worksheets(1).Copy Before:=.Worksheets(1)
    End With
    
    'If source sheet isn't the only visible sheet delete it
    
    numVisible = 0
    For Each ws In sourceWorkbook.Worksheets
        If ws.Visible Then numVisible = numVisible + 1
    Next
    If numVisible >= 2 Then
        Application.DisplayAlerts = False
        sourceWorkbook.Worksheets(1).Delete
        Application.DisplayAlerts = True
    End If
    
    'Save and close source and destination workbooks
    
    sourceWorkbook.Close SaveChanges:=True
    destWorkbook.Close SaveChanges:=True
    
End Sub