WHY THIS CLOSES EXCEL IF THERE´S NO SHEET NAMED AS...???
 

Hi, i´ve got a problem with this command button.
It deletes sheets named Tarea+nº and Acción+nº and works perfect
when these sheets already exist, but once i´ve deleted them i-ve got
an error when pussing again the button and excel gets closed asking me
if i want to send the error report.
I´ve already tried writing on error resume next everywhere (i´m a
beginner and don´t know the properly use of this function)


ANY IDEA??? THANKS A LOT!!!!


Private Sub CommandButton12_Click()


Dim wksH As Worksheet
Dim mtr(), n


For Each wksH In Worksheets
If Left(wksH.Name, 6) = "Acción" Then
n = n + 1
ReDim Preserve mtr(1 To n)
mtr(n) = wksH.Name
End If
Next
Worksheets(mtr()).Select


Set wksH = Nothing


For Each wksH In Worksheets
If Left(wksH.Name, 5) = "Tarea" Then
n = n + 1
ReDim Preserve mtr(1 To n)
mtr(n) = wksH.Name
End If
Next
Worksheets(mtr()).Select


Set wksH = Nothing
ActiveWindow.SelectedSheets.Delete


On Error Resume Next


End Sub

WHY THIS CLOSES EXCEL IF THERE´S NO SHEET NAMED AS...???
 

The problem seems to be that if you build an array of sheet names, and some
do not exist, none get selected.

Just change the code to delete them as you go

Private Sub CommandButton12_Click()

On Error Resume Next
Application.DisplayAlerts = False
For Each wksH In Worksheets
If Left(wksH.Name, 6) = "Acción" Or _
Left(wksH.Name, 5) = "Tarea" Then
wksH.Delete
End If
Next
Application.DisplayAlerts = True
On Error GoTo 0

Set wksH = Nothing

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"cbm" wrote in message
ups.com...
Hi, i´ve got a problem with this command button.
It deletes sheets named Tarea+nº and Acción+nº and works perfect
when these sheets already exist, but once i´ve deleted them i-ve got
an error when pussing again the button and excel gets closed asking me
if i want to send the error report.
I´ve already tried writing on error resume next everywhere (i´m a
beginner and don´t know the properly use of this function)


ANY IDEA??? THANKS A LOT!!!!


Private Sub CommandButton12_Click()


Dim wksH As Worksheet
Dim mtr(), n


For Each wksH In Worksheets
If Left(wksH.Name, 6) = "Acción" Then
n = n + 1
ReDim Preserve mtr(1 To n)
mtr(n) = wksH.Name
End If
Next
Worksheets(mtr()).Select


Set wksH = Nothing


For Each wksH In Worksheets
If Left(wksH.Name, 5) = "Tarea" Then
n = n + 1
ReDim Preserve mtr(1 To n)
mtr(n) = wksH.Name
End If
Next
Worksheets(mtr()).Select


Set wksH = Nothing
ActiveWindow.SelectedSheets.Delete


On Error Resume Next


End Sub

WHY THIS CLOSES EXCEL IF THERE´S NO SHEET NAMED AS...???
 

THAT WORKED GREAT!!!
Thanks a lot Bob, nice job

Bob Phillips ha escrito:

The problem seems to be that if you build an array of sheet names, and some
do not exist, none get selected.

Just change the code to delete them as you go

Private Sub CommandButton12_Click()

On Error Resume Next
Application.DisplayAlerts = False
For Each wksH In Worksheets
If Left(wksH.Name, 6) = "Acción" Or _
Left(wksH.Name, 5) = "Tarea" Then
wksH.Delete
End If
Next
Application.DisplayAlerts = True
On Error GoTo 0

Set wksH = Nothing

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"cbm" wrote in message
ups.com...
Hi, i´ve got a problem with this command button.
It deletes sheets named Tarea+nº and Acción+nº and works perfect
when these sheets already exist, but once i´ve deleted them i-ve got
an error when pussing again the button and excel gets closed asking me
if i want to send the error report.
I´ve already tried writing on error resume next everywhere (i´m a
beginner and don´t know the properly use of this function)


ANY IDEA??? THANKS A LOT!!!!


Private Sub CommandButton12_Click()


Dim wksH As Worksheet
Dim mtr(), n


For Each wksH In Worksheets
If Left(wksH.Name, 6) = "Acción" Then
n = n + 1
ReDim Preserve mtr(1 To n)
mtr(n) = wksH.Name
End If
Next
Worksheets(mtr()).Select


Set wksH = Nothing


For Each wksH In Worksheets
If Left(wksH.Name, 5) = "Tarea" Then
n = n + 1
ReDim Preserve mtr(1 To n)
mtr(n) = wksH.Name
End If
Next
Worksheets(mtr()).Select


Set wksH = Nothing
ActiveWindow.SelectedSheets.Delete


On Error Resume Next


End Sub