|
此れって、「WorkSheetsコレクション」とか「For Each 〜 Next」の問題じゃなくて
ただ単に、「Cells(i, 6).Value」の値の問題では無いの?
詰まり、シート名とCells(i, 6).Valueの値が、
全角、半角、大文字、小文字等が違っているのでは?
もしそうなら、以下の様にして見たら
Option Explicit
Option Compare Text '★この行追加
Public Sub SheetsDelete1()
Dim Sh As Worksheet
Dim i As Long
Dim Shname As Variant
Dim wdummy As String
For i = 5 To 29 Step 2
With Sheets("Menu")
If .Cells(i, 7).Value = "" And .Cells(i, 6).Value <> "" Then
Shname = .Cells(i, 6).Value
For Each Sh In Worksheets
If Not (Sh.Name = "Menu" Or Sh.Name = "基準管理") Then
If Sh.Name = Shname Then
MsgBox Shname & "が削除されます"
' Application.DisplayAlerts = False
' ActiveWindow.SelectedSheets.Delete
' Application.DisplayAlerts = True
End If
End If
Next Sh
End If
End With
Next i
End Sub
若しくは、比較を「=」では無く、
「StrComp」で「vbTextCompare」を指定して比較するとか?
'Option Explicit
Public Sub SheetsDelete2()
Dim Sh As Worksheet
Dim i As Long
Dim Shname As Variant
Dim wdummy As String
For i = 5 To 29 Step 2
With Sheets("Menu")
If .Cells(i, 7).Value = "" And .Cells(i, 6).Value <> "" Then
Shname = .Cells(i, 6).Value
For Each Sh In Worksheets
If StrComp(Sh.Name, "Menu", vbTextCompare) <> 0 _
And StrComp(Sh.Name, "基準管理", vbTextCompare) <> 0 Then
If StrComp(Sh.Name, Shname, vbTextCompare) = 0 Then
MsgBox Shname & "が削除されます"
' Application.DisplayAlerts = False
' ActiveWindow.SelectedSheets.Delete
' Application.DisplayAlerts = True
End If
End If
Next Sh
End If
End With
Next i
End Sub
|
|