Not able to delete worksheet after adding it earlier

[chrissieboy15]

Last Friday I knew nothing about VBA, so I'm maybe a bit noobish but here we go. Currently using Excel 2007.

First, I add an extra sheet to another workbook (datasource), then later I want to delete the exact same sheet. However, VBA doesn't seem to be able to remove any of the worksheets that are present upon opening the workbook. No errormessage, nothing.

Perhaps it's worth noting that in between the two subs, I use the datasource for a mail merge.

Here's the code that makes the worksheet in the first place

Sub prepare_datasource(datasource_columnhead_partnername As String, datasource_columnhead_attachments As String, datasource_worksheettemp As String)

Dim ws_Controlpanel As Excel.Worksheet
Set ws_Controlpanel = ThisWorkbook.Worksheets("Controlpanel")
Dim ws_Temp As Excel.Worksheet
Set ws_Temp = ThisWorkbook.Worksheets("Temp")

'----------------------------
' Prepare datasource
'----------------------------
'Application.ScreenUpdating = False

Dim oe_datsource As Excel.Application
Dim wb_oe_datsource As Excel.Workbook
Dim ws_oe_datsource As Excel.Worksheet
Dim wsname As String
Dim wstemp As Excel.Worksheet

Dim filterfield1 As Integer
Dim filterfield2 As Integer
Dim uniquearray As Variant

ws_Temp.Cells.ClearContents


' open datasource
Set oe_datsource = New Excel.Application
oe_datsource.Workbooks.Open ws_Controlpanel.Cells(findCellRow("choose_datasource"), findCellColumn("choose_datasource")) ' Open datasource
Set wb_oe_datsource = oe_datsource.ActiveWorkbook
wsname = ws_Controlpanel.OLEObjects("cmbDatasourceWorksheet").Object.Value
Set ws_oe_datsource = wb_oe_datsource.Worksheets(wsname)

Set wstemp = Nothing
On Error Resume Next
Set wstemp = wb_oe_datsource.Worksheets(datasource_worksheettemp)
On Error GoTo 0
If wstemp Is Nothing Then
    wb_oe_datsource.Worksheets.Add.Name = datasource_worksheettemp
    Set wstemp = wb_oe_datsource.Worksheets(datasource_worksheettemp)
End If

lc = ws_oe_datsource.Cells.SpecialCells(xlCellTypeLastCell).Column
lr = ws_oe_datsource.Cells.SpecialCells(xlCellTypeLastCell).Row
Set r1 = ws_oe_datsource.Range(ws_oe_datsource.Cells(1, 1), ws_oe_datsource.Cells(1, lc))    ' returns range in the column with filterfield
Set f1 = r1.Find(datasource_columnhead_partnername, , , xlWhole)                             ' returns field
Set f2 = r1.Find(datasource_columnhead_attachments, , , xlWhole)                             ' returns field

' MsgBox "lr: " & lr & " lc: " & lc & " f1: " & f1 & " f2: " & f2

filterfield1 = f1.Column ' columnnumber of datasource_columnhead_single     ' in this case partnername
filterfield2 = f2.Column ' columnnumber of datasource_columnhead_multiple   ' in this case attachments

' get the partnername column and copy to Macro Workbook
uniquearray = ws_oe_datsource.Range(ws_oe_datsource.Cells(1, filterfield1), ws_oe_datsource.Cells(lr, filterfield1)).Value
ws_Temp.Range(ws_Temp.Cells(1, 1), ws_Temp.Cells(lr, 2)).Value = uniquearray

' get the attachments column and copy to Macro Workbook
uniquearray = ws_oe_datsource.Range(ws_oe_datsource.Cells(1, filterfield2), ws_oe_datsource.Cells(lr, filterfield2)).Value
ws_Temp.Range(ws_Temp.Cells(1, 2), ws_Temp.Cells(lr, 2)).Value = uniquearray

' make partnerlist unique
ws_oe_datsource.AutoFilterMode = False
    ws_oe_datsource.Range(ws_oe_datsource.Cells(1, 1), ws_oe_datsource.Cells(lr, lc)).AutoFilter
    ws_oe_datsource.Range(ws_oe_datsource.Cells(1, filterfield1), ws_oe_datsource.Cells(lr, filterfield1)).AdvancedFilter _
    Action:=xlFilterInPlace, Unique:=True
    
ws_oe_datsource.Range(ws_oe_datsource.Cells(1, 1), ws_oe_datsource.Cells(lr, lc)).Copy _
    Destination:=wstemp.Range(wstemp.Cells(1, 1), wstemp.Cells(lr, lc))


'close opened datasource-object
wb_oe_datsource.Close SaveChanges:=True
Set wb_oe_datsource = Nothing
oe_datsource.Quit
Set oe_datsource = Nothing

'Application.ScreenUpdating = True

'MsgBox "Dataset is prepared"

Here's the code that tries to delete the worksheet later on. Note that adding and removing the "wtf" sheet is no problem.

Sub data_restore(datasource_worksheettemp As String)
' deletes the tempsheet named datasource_worksheettemp in the datasource
Dim ws_Controlpanel As Excel.Worksheet
Set ws_Controlpanel = ThisWorkbook.Worksheets("Controlpanel")

'Application.ScreenUpdating = True
Dim oe_datsource As Excel.Application
Dim wb_oe_datsource As Excel.Workbook
Dim ws_oe_datsource As Excel.Worksheet
Dim wstemp As Excel.Worksheet

'Set wb_oe_datsource = Nothing
'Set oe_datsource = Nothing
' open datasource
Set oe_datsource = New Excel.Application
oe_datsource.Workbooks.Open ws_Controlpanel.Cells(findCellRow("choose_datasource"), findCellColumn("choose_datasource")) ' Open datasource
Set wb_oe_datsource = oe_datsource.ActiveWorkbook


Set wstemp = Nothing
On Error Resume Next
Set wstemp = wb_oe_datsource.Worksheets(datasource_worksheettemp)
On Error GoTo 0


' Application.DisplayAlerts = False
            With wb_oe_datsource
                .Worksheets.Add.Name = "wtf"        'works
                .Worksheets("wtf").Delete              ' works
                .Worksheets("Sheet1temp").Delete  ' no response
            End With
' Application.DisplayAlerts = True
    
'close opened datasource-object
wb_oe_datsource.Close SaveChanges:=True
Set wb_oe_datsource = Nothing
oe_datsource.Quit
Set oe_datsource = Nothing

'Application.ScreenUpdating = True
MsgBox "Done"
End Sub

I hope you guys can help me out. Normally I like fixing things that don't work, but no errormessages whatsoever is leaving me clueless.