I have the following code:

Sub CopySheet()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    
    Set wb1 = Workbooks("TestData.xlsm")
    Set wb2 = Workbooks("AutoBE.xlsm")
    
    wb1.Sheets("Sheet1").Copy After:=wb2.Sheets("Sheet1")
       
End Sub

This manages to copy Sheet1 from TestData into the AutoBE workbook. However, it creates a new sheet due to the Copy After

Is there a way to insert the data into an existing worksheet in AutoBE such as Sheet1 itself, assuming for simplicity that it is empty? I have looked everywhere online and forums and no luck. Is this even possible? With experience with python I fell as though it must be.

Alternatively is there a way to create this new sheet in AutoBE as this code does but then deleting all other sheets in AutoBE apart from the newly created one?

CodePudding user response：

'Replace' Worksheet

• Here is a simple example of how you could proceed.
• If this doesn't work for you, share what's wrong with it so we can improve.

A Quick Fix

Sub CopyWorksheet()
    
    ' Source
    
    Dim swb As Workbook: Set swb = Workbooks("TestData.xlsm")
    Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1")
    
    ' Destination
    
    Dim dwb As Workbook: Set dwb = Workbooks("AutoBE.xlsm")
    ' Reference the old worksheet...
    Dim dwsOld As Worksheet: Set dwsOld = dwb.Worksheets("Sheet1")
    ' and store its name and its code name in variables.
    Dim dName As String: dName = dwsOld.Name
    Dim dCodeName As String: dCodeName = dwsOld.CodeName
    
    ' Copy.
    
    sws.Copy After:=dwsOld
    
    ' Reference the new (copied) worksheet.
    Dim dwsNew As Worksheet: Set dwsNew = dwsOld.Next
    
    ' Delete the old worksheet.
    Application.DisplayAlerts = False ' delete without confirmation
        dwsOld.Delete
    Application.DisplayAlerts = True
    
    ' Rename the new worksheet and restore the old code name.
    dwsNew.Name = dName
    dwb.VBProject.VBComponents(dwsNew.CodeName).Name = dCodeName
       
End Sub