Copy worksheet between workbooks and overwrite the current worksheet

Question

I have written VBA code that opens up a destination workbook, copies one of the worksheets, and pastes it into the current workbook.

When I run it a second or third time etc... instead of overwriting the current worksheet, it creates a completely new one.

Ex: Worksheet is called "data", first time it transfers "data", second time "data(2)".

I have another worksheet that uses VLOOKUP function to look at some cells of this data worksheet, so it is crucial that it has correct name "data".

I thought about deleting the current (data) file before running the macro, but what if something crashes and I lose my worksheet? Is there a better solution?

NOTE: I am running the macro from the main workbook to get the sheet to be copied from the external workbook.

Sub UpdateT()

    Dim wb As Workbook
    Dim aw As Workbook

    'Open 2nd Workbook
    Set aw = Application.ActiveWorkbook
    Set wb = Workbooks.Open(Filename:="C:\Users\yilmadu00\Desktop\T.xlsx")

    'Copy To Different Workbook
    wb.Sheets("data").Copy After:=aw.Sheets("Data1")

    'Close 2nd Workbook
    aw.Save
    wb.Close

    aw.Sheets("data").Visible = False
    ActiveWorkbook.Protect ("Password")

End Sub

Answer 1

Function to check whether worksheet exists (credits to @ScottCrainer):

Function SheetExists(ws As String)
    SheetExists = Not IsError(Application.Evaluate(ws & "!A1"))
End Function

It does have the issue: if A1 on the sheet contains an error it will return a false negative.

Answer 2

ActiveWorkbook vs ThisWorkbook, Sheets vs Worksheets

You have used 'Activeworkbook' and 'Sheet(s)' in the code so I played along.

But

Although you can have a third workbook to run the code from, I'm guessing you are running the code from a module in the 'ActiveWorkbook'. If this is true, it would be more correct to use 'ThisWorkbook' instead which always refers to the workbook that contains the code (module), to avoid accidentally running the code on a third workbook.

Sheet(s) refers to Worksheet(s) and Chartsheet(s), again I'm guessing there are no chartsheets involved in this code, therefore it would be more correct to use 'Worksheet(s)' instead of 'Sheet(s)'.

Sub UpdateT()

  Const cStrPath As String = "C:\Users\yilmadu00\Desktop\T.xlsx"
  Const cStrAfter As String = "Data1"
  Const cStrName As String = "data"
  Const cStrOld As String = "data_old"

  Dim aw As Workbook '1st workbook, 'ActiveWorkbook'
  Dim wb As Workbook '2nd workbook
  Dim oWs As Sheet 'Each sheet in workbook 'aw'
  Dim blnFound As Boolean 'True if sheet(cStrName) was found

  Set aw = ActiveWorkbook 'Create a reference to the ActiveWorkbook
  Set wb = Workbooks.Open(Filename:=cStrPath) 'Open 2nd Workbook

  With aw
  '  .UnProtect ("Password")
    'Check each sheet in workbook 'aw'.
    For Each oWs In aw.Sheets
      With oWs
        'Check if there already is a sheet with the name 'cStrName'.
        If .Name = cStrName Then
          .Name = cStrOld 'Rename the sheet.
          blnFound = True 'Sheet(cStrName) was found.
          Exit For 'Immediately stop checking, there can only be one.
        End If
      End With
    Next
  End With

  With wb
    'Copy sheet from 2nd workbook ('wb') to workbook 'wa'.
    .Sheets(cStrName).Copy After:=aw.Sheets(cStrAfter)
    .Close 'Close 2nd workbook ('wb').
  End With

  With aw
    With Application
      If blnFound = True Then 'Sheet(cStrName) was found.
        .DisplayAlerts = False 'Disable showing delete message.
        aw.Sheets(cStrOld).Delete 'Delete old version of sheet.
        .DisplayAlerts = True
      End If
    End With
    .Sheets(cStrName).Visible = False 'Hide sheet named 'cStrName'
    .Protect ("Password")
    .Save 'Save workbook 'aw'.
  End With

End Sub

The next time you want to do something with the sheet you have to unprotect it or the code will fail. Hidden sheets can be deleted with no problems.