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!
[/CODE][/CODE]
VBA Code:
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]