Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
2012to2016
2012to2016
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Anzeige
Inhaltsverzeichnis

Makro für Blätter ein- und ausblenden vereinfachen

Forumthread: Makro für Blätter ein- und ausblenden vereinfachen

Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 16:14:50
Soham235
Ich habe ein Kassabuch für 20 Klienten erstellt. Jeder Klient befindet sich auf einem eigenen Blatt von "KL 1" bis "KL 20". Am ersten Blatt ("KASSASTAND"), in Zelle "N3" befindet sich der Wert, der darüber bestimmt, wie viele Blätter geöffnet werden. Das Ein- und Ausblenden der Blätter funktioniert zwar mit diesem Makro recht gut, erzeugt aber einerseits ein "Ruckeln" auf dem Bildschirm. Andererseits würde ich gerne das Makro verkürzen, um eventuell noch weitere KL hinzuzufügen und auch um den Rechner nicht allzu sehr zu belasten. Da ich in VSB noch recht unerfahren bin, würde mich freuen, wenn jemand eine Idee hätte, wie ich dieses Makro vereinfachen bzw. verkürzen könnte:

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("N3").Value = "1" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "2" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "3" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "4" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "5" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "6" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "7" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "8" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "9" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "10" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "11" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "12" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "13" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = True
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "14" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = True
ThisWorkbook.Worksheets("KL 14").Visible = True
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "15" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = True
ThisWorkbook.Worksheets("KL 14").Visible = True
ThisWorkbook.Worksheets("KL 15").Visible = True
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "16" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = True
ThisWorkbook.Worksheets("KL 14").Visible = True
ThisWorkbook.Worksheets("KL 15").Visible = True
ThisWorkbook.Worksheets("KL 16").Visible = True
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "17" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = True
ThisWorkbook.Worksheets("KL 14").Visible = True
ThisWorkbook.Worksheets("KL 15").Visible = True
ThisWorkbook.Worksheets("KL 16").Visible = True
ThisWorkbook.Worksheets("KL 17").Visible = True
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "18" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = True
ThisWorkbook.Worksheets("KL 14").Visible = True
ThisWorkbook.Worksheets("KL 15").Visible = True
ThisWorkbook.Worksheets("KL 16").Visible = True
ThisWorkbook.Worksheets("KL 17").Visible = True
ThisWorkbook.Worksheets("KL 18").Visible = True
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "19" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = True
ThisWorkbook.Worksheets("KL 14").Visible = True
ThisWorkbook.Worksheets("KL 15").Visible = True
ThisWorkbook.Worksheets("KL 16").Visible = True
ThisWorkbook.Worksheets("KL 17").Visible = True
ThisWorkbook.Worksheets("KL 18").Visible = True
ThisWorkbook.Worksheets("KL 19").Visible = True
ThisWorkbook.Worksheets("KL 20").Visible = False


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


If Range("N3").Value = "20" Then
ThisWorkbook.Worksheets("KL 1").Visible = True
ThisWorkbook.Worksheets("KL 2").Visible = True
ThisWorkbook.Worksheets("KL 3").Visible = True
ThisWorkbook.Worksheets("KL 4").Visible = True
ThisWorkbook.Worksheets("KL 5").Visible = True
ThisWorkbook.Worksheets("KL 6").Visible = True
ThisWorkbook.Worksheets("KL 7").Visible = True
ThisWorkbook.Worksheets("KL 8").Visible = True
ThisWorkbook.Worksheets("KL 9").Visible = True
ThisWorkbook.Worksheets("KL 10").Visible = True
ThisWorkbook.Worksheets("KL 11").Visible = True
ThisWorkbook.Worksheets("KL 12").Visible = True
ThisWorkbook.Worksheets("KL 13").Visible = True
ThisWorkbook.Worksheets("KL 14").Visible = True
ThisWorkbook.Worksheets("KL 15").Visible = True
ThisWorkbook.Worksheets("KL 16").Visible = True
ThisWorkbook.Worksheets("KL 17").Visible = True
ThisWorkbook.Worksheets("KL 18").Visible = True
ThisWorkbook.Worksheets("KL 19").Visible = True
ThisWorkbook.Worksheets("KL 20").Visible = True


Else
ThisWorkbook.Worksheets("KL 1").Visible = False
ThisWorkbook.Worksheets("KL 2").Visible = False
ThisWorkbook.Worksheets("KL 3").Visible = False
ThisWorkbook.Worksheets("KL 4").Visible = False
ThisWorkbook.Worksheets("KL 5").Visible = False
ThisWorkbook.Worksheets("KL 6").Visible = False
ThisWorkbook.Worksheets("KL 7").Visible = False
ThisWorkbook.Worksheets("KL 8").Visible = False
ThisWorkbook.Worksheets("KL 9").Visible = False
ThisWorkbook.Worksheets("KL 10").Visible = False
ThisWorkbook.Worksheets("KL 11").Visible = False
ThisWorkbook.Worksheets("KL 12").Visible = False
ThisWorkbook.Worksheets("KL 13").Visible = False
ThisWorkbook.Worksheets("KL 14").Visible = False
ThisWorkbook.Worksheets("KL 15").Visible = False
ThisWorkbook.Worksheets("KL 16").Visible = False
ThisWorkbook.Worksheets("KL 17").Visible = False
ThisWorkbook.Worksheets("KL 18").Visible = False
ThisWorkbook.Worksheets("KL 19").Visible = False
ThisWorkbook.Worksheets("KL 20").Visible = False


End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub
Anzeige

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 16:36:29
BoskoBiati
Hi,

als einfacher Ansatz: 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)



Dim loA As Long

Dim loB As Long

loB = Sheets.Count - 1

If Intersect(Target, Range("N3")) Is Nothing Then Exit Sub



With ThisWorkbook

For loA = 1 To Target.Value

.Sheets("KL " & loA).Visible = True

Next

For loA = Target.Value + 1 To loB

.Sheets("KL " & loA).Visible = False

Next

End With

End Sub


Gruß

Edgar
Anzeige
AW: Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 16:38:08
Alwin Weisangler
Hallo,

ins Modul des Tabellenblattes "KASSASTAND": 



Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Wks As Worksheet

    If Not Intersect(Target, Range("N3")) Is Nothing Then

        For Each Wks In ThisWorkbook.Sheets

            If Replace(Wks.Name, "KL ", "") = Target Then

                Wks.Visible = xlSheetVisible

            Else

                If Wks.Name > "KASSASTAND" Then Wks.Visible = xlSheetVeryHidden

            End If

        Next

    End If

End Sub


Gruß Uwe
Anzeige
AW: Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 16:54:16
BoskoBiati
Hallo Uwe,

war die Aufgabe nicht, die Blätter KL 1 bis KL (N3) sichtbar zu machen?

Gruß

Edgar
AW: Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 17:03:52
Alwin Weisangler
Ja, dass, hatte ich übersehen, dann in der Abfrage also = statt =.

Gruß Uwe
AW: Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 16:39:43
Daniel
Hi

Nutze Variablen und Schleifen.
Das True/False für das .visible kann man auch berechnen.
Bei dieser Variante ist die Anordnung der Blätter das entscheidende, nicht der Name 

Dim i as Long

Application.Screenupdating = false

With ThisWorkbook

For i = 2 to .sheets.count

    .sheets(i).visible = (i - 1) = CLng(.Sheets(1).Range("N1").Value)

Next

End With

Application.screenupdating = true


Das ScreenUpdating bestimmt, ob während des Makrolaufs die Anzeige aktualisiert wird
Das ausschalten sollte das Flackern verhindern.

Gruß Daniel
Anzeige
AW: Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 18:22:26
Soham235
Danke dir! Funzt jetzt problemlos!
AW: Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 19:36:44
Soham235
Hallo Daniel, danke nochmals für deine Lösung!
Hab jetzt aber leider noch eine Folgefrage:
Wie gesagt, das Makro funzt wunderbar. Ich würde aber noch gerne alle Blätter vor Löschung und Umbenennung schützen - der Blattschutz alleine reicht da nicht. Wenn ich aber im Excel-Menü "Arbeitsmappe schützen" aktiviere, crasht das Makro mit der Fehlermeldung "Laufzeitfehler '1004' Die Visible-Eigenschaft des Worksheet-Objektes kann nicht festgelegt werden."
Hättest du hierzu eventuell noch eine Idee?
Anzeige
AW: Makro für Blätter ein- und ausblenden vereinfachen
29.06.2025 21:09:33
Daniel
Makros dürfen nur das tun, was normale Anwender auch tun dürfen.
Wenn du die Mappe geschützt hast, kannst du an der Blattstruktur nichts mehr ändern.
Aber du kannst den Schutz aufheben, wenn du das Passwort kennst.
Dh du musst im Makro per Code den Schutz vorher aufheben und hinterher wieder aktivieren.
Wie du das programmierst, kannst du leicht selbst mit Hilfe des Recorders herausfinden.
Das Lesen der Hilfeseite von Microsoft zu diesem Befehl ist immer empfehlenswert.

Gruß Daniel

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Live-Forum - Die aktuellen Beiträge
Datum
Titel
14.05.2026 13:31:09
Zeichen ausblenden
14.05.2026 09:50:42
Makro für MacOs
13.05.2026 19:14:18
Gleichheitszeichen
13.05.2026 14:24:56
VBA Projekt Passwort automatisch eingeben
13.05.2026 08:49:41
Reale Berechnung ohne Nachkommawerte
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum