I just want to get the range from A1:A80 from a sheet called "Children" but I debug it, it gets nothing. Here's my code below:

   Dim wks2 As Worksheet
   Dim children As Range
   Worksheets("Children").Activate
   Set wks2 = Worksheets("Children")
   Set children = wks2.Range("A1:A80")
   ResetChild (children) //The sub that I want to call. Explanation bellow.

In the function ResetChild I want to delete all the sheets in my Workbook, that have names equal to the first column of the sheet Children. (A1:A80) So here's my sub below:

Sub ResetChild(children As Range)
Call DisableCalculations
Dim cell As Range
    
For Each cell In children
    Set childrenSheet = Worksheets(cell.Value)
        If (DoesSheetExist(childrenSheet)) Then
            Application.DisplayAlerts = False
            Sheets(childrenSheet).Delete
            Application.DisplayAlerts = True
        End If
End Sub

CodePudding user response：

Your code should work. How do you know that it returns nothhing? put the row in the end of your code

   Debug.Print children.Count

if it returns >0 then all ok and children is the range object with data

CodePudding user response：

Maybe try something like this:

Sub DeleteChildren()
    Dim children As Range
    Dim oChildSheet As Worksheet
    Dim vChildName

    Set children = ThisWorkbook.Worksheets("Children").Range("A1:A80")        
    For Each vChildName In children
        Set oChildSheet = GetChildSheet(CStr(vChildName))
        If Not oChildSheet Is Nothing Then
            Application.DisplayAlerts = False
            Call oChildSheet.Delete
            Application.DisplayAlerts = True
        End If
    Next vChildName
End Sub

Function GetChildSheet(sChildName As String) As Worksheet
    On Error GoTo errHandler
    Set GetChildSheet = ThisWorkbook.Worksheets(sChildName)
    Exit Function
errHandler:
    Debug.Print "Child sheet not found: " & sChildName
End Function