CODEWITHSUNDEEP
excelvba
user5783544
user5783544

Reputation:

Can't find a worksheet which exists

I have a worksheet in Excel with the name "Control". I'm receiving a msgbox saying it doesn't exists. After I click on "OK" I get an error of "invalid call of function or procedure and the debugger stops in this function:

Private Sub ClearData(dataSheet As Worksheet)
    'On Error Resume Next
        'dataSheet.rows(DataRow1 & ":" & dataSheet.rows.Count).SpecialCells(xlCellTypeConstants).ClearContents
        Sheets(dataSheet).UsedRange.ClearContents
End Sub

This function is used to clear the worksheet and the code before 'dataSheet.rows(DataRow1 & ":" & dataSheet.rows.Count).SpecialCells(xlCellTypeConstants).ClearContents is commented because is raises error and I decided to modify to the line Sheets(dataSheet).UsedRange.ClearContents but the problem persists.

EDIT ClearData is called with this code:

Public Sub Init(rowNr As Integer, copyMap As CopyActionsMap, dataSheet As Worksheet)

    m_configRowNr = rowNr
    Set m_dataSheet = dataSheet
    m_dataRowNr = FindDataRow
    m_dataSheet.Cells(m_configRowNr, 1).Select 'select first cell in config row
    If (Not m_initialized) Then Set m_columnCopyConfigs = GetColumnCopyConfigs(copyMap) 'also sets m_count
    ClearData (m_dataSheet) 'Clean the existing data Now it says "object doenst support this method or property" after this: Private Sub ClearData(dataSheet As Worksheet) Sheets(dataSheet).Cells.Delete

End Sub

Upvotes: 2

Views: 245

Answers (1)

Netloh
Netloh

Reputation: 4378

As @tigeravatar has mentioned in the comments below your question, you are trying to use a worksheet object as a string variable.

Try changing your code to

Private Sub ClearData(dataSheet As Worksheet)
    'On Error Resume Next
        'dataSheet.rows(DataRow1 & ":" & dataSheet.rows.Count).SpecialCells(xlCellTypeConstants).ClearContents
        dataSheet.UsedRange.ClearContents
End Sub

If you want to clear the sheet by a specific string name instead, you should change your code to

Private Sub ClearData(dataSheet As String)
    'On Error Resume Next
        'dataSheet.rows(DataRow1 & ":" & dataSheet.rows.Count).SpecialCells(xlCellTypeConstants).ClearContents
        Sheets(dataSheet).UsedRange.ClearContents
End Sub

And you can then clear the sheet named "Test Sheet" by calling

ClearData "Test Sheet"

Upvotes: 2

Related Questions