I am trying to copy a worksheet from a closed workbook into an open workbook but it is not working. I don't get any error but nothing happens. Please see my code comments also.
Code:
Set xlWrkBk2 = Workbooks.Open("M:\BSC Benefits\Benefits Tickets\" & Format(Date, "YYYY_MM_DD") & "Empire Report.xlsx")
xlWrkBk2.UnProtect
xlWrkBk2.UnprotectSharing
Set targetBk = ActiveWorkbook 'This should be the workbook I just opened above rihgt?
Set getReconFile = Application.FileDialog(msoFileDialogOpen) 'open a file dialog to choose the workbook that has the sheet(s) I want to copy
With getReconFile
.Show
Set reconBk = GetObject(getReconFile)
For Each Sheet In reconBk.Sheets
If Sheet.Visible = True Then
Sheet.Copy After:=targetBk.Sheets(targetBk.Sheets.count) 'copy the sheet from the file I just selected and add it as the last sheet of file opened earlier.
ActiveSheet.Name = "Sub-Group Translator" 'give a name to the sheet that I just copied over
' End If
'Next Sheet
'End With
xlWrkBk2.Save 'Save WorkBook
xlWrkBk2.Close 'Close WorkBook