copy/overwrite sheet from another workbook

Question

I want to copy a sheet from another workbook while overwriting an existing sheet.

I can copy the sheet but it creates a new one. The target workbook contains other sheets, which are using the already in wb_target existing sheet (that is supposed to be overwritten) to reference data to be evaluated; so "delete", "rename" won't work because the references are lost after deleting the old sheet.

Here the code:

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
    .Show
    fullpath = .SelectedItems.Item(1)
End With
If InStr(fullpath, ".xls") = 0 Then
    Exit Sub
End If
Workbooks.Open fullpath

Sheets("ws_source").Copy Before:=Workbooks("wb_target.xlsm").Sheets("ws_target")

newfilename = Left(fullpath, Len(fullpath) - 4) & "_new.xls"
ActiveWorkbook.SaveAs Filename:=newfilename

This code works but I need to overwrite ws_target instead of creating ws_source in wb_target.

Answer 1

First way

1. Copy the sheet
   2. Save desired name as variable
   3. Delete the sheet you want to delete
   4. Rename the sheet you copied using the previously stored name variable.

You can't physically 'overwrite a sheet'.

Second way

You could also do it in another way:

1. Delete all contents from destination sheet with something like this:

   ThisWorkbook.Worksheets("TestTestDestination").Cells.Clear
   

2. Copy all contents from source sheet to destination sheet

   Sub test_copy_sheet_overwrite()
   Dim arr1()
   
   ' Copies the source sheet's contents into an array
   arr1() = ThisWorkbook.Worksheets("TestTestsource").UsedRange.Value
   
   ' Pastes the array into the destination sheet
   ThisWorkbook.Worksheets("TestTestDestination").Cells(1, 1).Resize(UBound(arr1, 1), UBound(arr1, 2)) = arr1()
   
   End Sub