Copy Sheet From Closed Workbook Into Existing Sheet (Overwrite) on another Workbook

Question

I've tried some VBA codes in here but always end up with a code that create a new sheet on destination workbook. What I want is to copy a sheet from closed workbook into existing sheet on an open workbook (overwrite).

Example:

• "Sheet1" from closed workbook "Book1"
• Copied to opened workbook "Book2" existing sheet named "Sheet2" without creating a new sheet.

Thanks

Sub COPYSHEET()

Dim wbTarget As Workbook
Dim wbSource As Workbook

Application.ScreenUpdating = False

Set wbTarget = ActiveWorkbook
Set wbSource = Workbooks.Open("C:\Users\user1\Downloads\Book1.xlsx")

wbSource.Sheets(1).Copy before:=wbTarget.Sheets(1)
wbSource.Close SaveChanges:=False

End Sub

Answer 1

wbSource.Sheets(1).Copy before:=wbTarget.Sheets(1) copies/inserts the respective sheet before the first sheet of wbTarget.

To copy its content, please try the next way:

 With wbTarget.Sheets(1)
   .UsedRange.Clear
   wbSource.Sheets(1).UsedRange.Copy .Range("A1")
   .UsedRange.EntireColumn.autofit
 End With