If sheet exists then...

[trevolly]

Hi all,

I have a workbook we use at work for a daily log and there are two versions - there is a Day and Night version. In this workbook is a worksheet called "Handover" that we copy from Day to Night work book and so on... I've put a button in to each workbook and attached a vba code to it that copies the "Handover" worksheet, opens the Night workbook, pastes the "Handover" sheet in to the Night workbook and then saves this workbook and close it.

The issue I have is that if a colleague presses the button multiple times then Excel copies the "Handover" sheet multiple times in to the Night workbook.... not knowing that the "Handover" worksheet has already been copied and pasted. This results in multiple "Handover" sheets (Handover2, Handover3) ect...

Would anyone be able to help as I'd like the vba code to be able to check if the "Handover" sheet already exists in the closed workbook and if so Excel doesn't copy the "Handover" sheet again and shows a message box saying it already exists.

Ive attached the vba code that was written for me - I'd really appreciate any help

Sub CopySheetDaytoNight()
'Handover Copy Day to Night
        Dim Ws As Worksheet
        Set Ws = Worksheets("Handover")
        Application.ScreenUpdating = False
    With Workbooks.Open(Replace(ActiveWorkbook.FullName, " Day.", " Night."))
         Ws.Copy , .Sheets("Closures")
        .Close True
    End With
        Application.ScreenUpdating = True
        Set Ws = Nothing
        MsgBox "Handover Sheet Copied"
End Sub

 

[Trebor76]

Hi trevolly,

Not fully tested but this should do the trick:

Option Explicit
Sub CopySheetDaytoNight()

    'Handover Copy Day to Night
    Dim Ws As Worksheet
    Dim wsTemp As Worksheet
    Dim wb As Workbook
    
    Application.ScreenUpdating = False
    
    Set Ws = Worksheets("Handover")
    Set wb = Workbooks.Open(Replace(ActiveWorkbook.FullName, " Day.", " Night."))
        
    On Error Resume Next
        Set wsTemp = wb.Sheets(CStr(Ws.Name))
        If Err.Number <> 0 Then
            Ws.Copy , wb.Sheets("Closures")
            wb.Close True
            MsgBox "Handover Sheet Copied"
        Else
            wb.Close False 'Close without saving any changes
            MsgBox "Tab """ & Ws.Name & """ already exists in workbook """ & wb.Name & """.", vbExclamation
        End If
    On Error GoTo 0
    
    Set wb = Nothing: Set Ws = Nothing: Set wsTemp = Nothing
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[trevolly]

Hey @Trebor76, thank you so much for the reply - this works great. This'll stop the colleagues who mash the Handover copy button and then wonder why there's multiple copies and then blame the form!!!

Thank you again.

Trevor

 

[Trebor76]

Hey @Trebor76, thank you so much for the reply - this works great. This'll stop the colleagues who mash the Handover copy button and then wonder why there's multiple copies and then blame the form!!!

Thank you again.

Trevor

Thanks for letting us know and you're welcome