CODEWITHSUNDEEP
excelvba
Mirkaminer
Mirkaminer

Reputation: 95

Worksheet function trouble

I have a version of this that works but this version dosen't

Public Const GlobalSheetName As String = "Sammanställning,Pris_Schrack,Pris_Pelco,Pris_Swansson,Pris_Övrig" 

Sub Tabortblad()
 Dim ws As Worksheet
 Dim ShName As String
 Dim SnNumm As Long
 char = Split(GlobalSheetName, ",")
 SnNumm = UBound(char) - LBound(char) + 1
    If Worksheets.Count = SnNumm Then    'bör ändra denna till större antal
       MsgBox "Där finns bara " & SnNumm & " blad"
    Else
       strCancel = MsgBox("Är du säker på att du vill radera bladen", vbOKCancel, "Radera Blad")
           If strCancel = "1" Then
              Application.ScreenUpdating = False
               Application.DisplayAlerts = False
                For Each ws In ActiveWorkbook.Worksheets
                   For Each char In Split(GlobalSheetName, ",")
                        If Not ws.Name = char Then
                           ws.Activate
                           Worksheets(ws.Name).Delete
                        End If
                   Next
                Next
                 Sheets("Sammanställning").Range("B:B").Value = ""
                 Sheets("Sammanställning").Range("P68:R" & Sheets("Sammanställning").Cells(Rows.Count, "R").End(xlUp).Row + 1).Value = ""
                 Application.DisplayAlerts = True
            End If
       Application.ScreenUpdating = True
       Worksheets("Sammanställning").Activate
       Range("A1").Select
     End If
End Sub

Now this is the version that works

Sub Tabortblad()
 Dim ws As Worksheet
 Dim ShName As String
 If Worksheets.Count = "5" Then    'bör ändra denna till större antal
    MsgBox "Där finns bara 5 blad"
 Else
    strCancel = MsgBox("Är du säker på att du vill radera bladen", vbOKCancel, "Radera Blad")
    If strCancel = "1" Then
       Application.ScreenUpdating = False
        Application.DisplayAlerts = False
         For Each ws In ActiveWorkbook.Worksheets
          If Not ws.Name = "Sammanställning" Then
          If Not ws.Name = "Pris_Schrack" Then
          If Not ws.Name = "Pris_Pelco" Then
          If Not ws.Name = "Pris_Swansson" Then
          If Not ws.Name = "Pris_Övrig" Then
            ws.Activate
               Worksheets(ws.Name).Delete
          End If
          End If
          End If
          End If
          End If
         Next
          Sheets("Sammanställning").Range("B:B").Value = ""
          Sheets("Sammanställning").Range("P68:R" & Sheets("Sammanställning").Cells(Rows.Count, "R").End(xlUp).Row + 1).Value = ""
         Application.DisplayAlerts = True
     End If
   Application.ScreenUpdating = True
   Worksheets("Sammanställning").Activate
   Range("A1").Select
  End If
End Sub

The problem is ws.Name it returns a fault in the first version and in the second it works fine.

I use this to delete the proper worksheets that I added in another sub. I'm using the constant to collect the sheet names so if I have to add one it's easier.

Upvotes: 0

Views: 53

Answers (3)

DisplayName
DisplayName

Reputation: 13386

you could consider deleting in one shot:

Dim ws As Worksheet
Dim names As String
For Each ws In ActiveWorkbook.Worksheets
    If InStr(GlobalSheetName, ws.Name) = 0 Then names = names & ws.Name & "|"
Next
If names <> "" Then Worksheets(Split(Left(names, Len(names) - 1), "|")).Delete

Upvotes: 0

user4039065
user4039065

Reputation:

Run through the worksheets as their count backwards.

dim w as long
For w=ActiveWorkbook.Worksheets.count to 1 step-1
  If iserror(application.match(Worksheets(w).Name, array("Sammanställning", "Pris_Schrack", "Pris_Pelco", "Pris_Swansson", "Pris_Övrig"), 0)) Then
       Worksheets(w).Delete
  End If
Next w

Be careful the the VBE does not mess up letters like ä or Ö by converting them to ?. The VBE doesn't handle unicode.

Upvotes: 0

Tony M
Tony M

Reputation: 1762

I confirmed that the first subroutine worked and then confirmed that I was unable to get the second, "not working" subroutine to work. Then, I was able to get the second subroutine to run completely through by adding the following:

option Explicit
Dim strCancel As String, char As Variant

First, you should always include "option explicit" at the top of your modules since it forces you to properly define your variables. This was the source of the problem since the char variable was not defined and some of the lines of your code expected an array, so it failed at that point. At least, that's what happened for me before I changed char to a Variant. You may not want to use a variant for char, and I suggest research into the lines of your code using this variable.

I can't help further because I'm not entirely clear what the goal of your code is and I don't understand the non-English words used. Let me know if you have more questions.

Upvotes: 1

Related Questions