{"id":3269,"date":"2023-11-05T12:20:57","date_gmt":"2023-11-05T12:20:57","guid":{"rendered":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/"},"modified":"2023-12-03T13:05:59","modified_gmt":"2023-12-03T13:05:59","slug":"zahle-die-blatter","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/","title":{"rendered":"Wie z\u00e4hlt man bl\u00e4tter mit vba?"},"content":{"rendered":"<div class=\"excel-vor-dem-inhalt\" id=\"excel-1268814056\"><script type=\"text\/javascript\">\r\n\tatOptions = {\r\n\t\t'key' : 'c1158f160081d6540a8409e6925dab94',\r\n\t\t'format' : 'iframe',\r\n\t\t'height' : 250,\r\n\t\t'width' : 300,\r\n\t\t'params' : {}\r\n\t};\r\n<\/script>\r\n<script type=\"text\/javascript\" src=\"\/\/www.highperformanceformat.com\/c1158f160081d6540a8409e6925dab94\/invoke.js\"><\/script><\/div> <p>Wenn Sie in Excel viele Bl\u00e4tter haben, k\u00f6nnen Sie diese mithilfe von VBA-Code schnell z\u00e4hlen, anstatt sie manuell zu z\u00e4hlen oder eine beliebige Formel zu verwenden. In diesem Artikel werden wir verschiedene M\u00f6glichkeiten zum Z\u00e4hlen der Bl\u00e4tter einer Arbeitsmappe sehen.<\/p><h2 class=\"wp-block-heading\"> Z\u00e4hlen Sie die Bl\u00e4tter in der aktiven Arbeitsmappe<\/h2><p> Hier ist der Code, den Sie zum Z\u00e4hlen des aktiven Arbeitsmappenblatts verwenden m\u00fcssen. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-52417\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png\" sizes=\"auto, \" srcset=\"\" alt=\"Z\u00e4hlbl\u00e4tter bilden den Aktivbinder\" width=\"675\" height=\"344\"><\/figure><\/div><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">ThisWorkbook.Sheets.Count<\/code><\/pre><p> In diesem Code haben Sie zun\u00e4chst mit \u201eThisWorkbook\u201c auf die aktive Arbeitsmappe verwiesen und auf alle Bl\u00e4tter verwiesen. Am Ende verwenden Sie die Z\u00e4hlmethode, um alle Bl\u00e4tter zu z\u00e4hlen. Und wenn Sie Arbeitsbl\u00e4tter statt Bl\u00e4tter z\u00e4hlen m\u00f6chten, verwenden Sie den folgenden Code.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">ThisWorkbook.Worksheets.Count<\/code><\/pre><p class=\"content-box-grey\"> <em>N\u00fctzliche Links: <a href=\"https:\/\/exceladvisor.org\/de\/ein-makro-ausfuhren-2\/\">Ausf\u00fchren eines Makros<\/a> \u2013 <a href=\"https:\/\/exceladvisor.org\/de\/ein-makro-aufzeichnen\/\">Makrorecorder<\/a> \u2013 <a href=\"https:\/\/exceladvisor.org\/de\/einfacher-visueller-editor\/\">Visual Basic Editor<\/a> \u2013 <a href=\"https:\/\/exceladvisor.org\/de\/personliches-makro-notizbuch\/\">Handbuch zu pers\u00f6nlichen Makros<\/a><\/em><\/p><h2 class=\"wp-block-heading\"> Z\u00e4hlen Sie Bl\u00e4tter aus einer anderen Arbeitsmappe<\/h2><p> Sie k\u00f6nnen den Namen der Arbeitsmappe als Referenz verwenden und dann die darin enthaltenen Bl\u00e4tter z\u00e4hlen. Angenommen, Sie m\u00f6chten die Bl\u00e4tter in der Arbeitsmappe \u201eBuch1\u201c z\u00e4hlen.<\/p><p> Hier ist der Code.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Workbooks(\"sample-file.xlsx\").Sheets.Count<\/code><\/pre><p> Dieser Code gibt Ihnen die Anzahl der Bl\u00e4tter in der Arbeitsmappe \u201esample-file.xlsx\u201c an. Es gibt eine Sache, die Sie mitnehmen m\u00fcssen: Dieser Ordner muss ge\u00f6ffnet sein.<\/p><h2 class=\"wp-block-heading\"> Z\u00e4hlen Sie die Bl\u00e4tter aller ge\u00f6ffneten Ordner<\/h2><p> Sie k\u00f6nnen mehrere Arbeitsmappen gleichzeitig ge\u00f6ffnet haben und alle Bl\u00e4tter in diesen Arbeitsmappen z\u00e4hlen.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Sub vba_loop_all_sheets() Dim wb As Workbook Dim i As Long For Each wb In Application.Workbooks If wb.Name &lt;&gt; \"PERSONAL.XLSB\" Then i = i + wb.Sheets.Count End If Next wb MsgBox \"Total sheets in all the open workbooks: \" &amp; i End Sub<\/code><\/pre><h2 class=\"wp-block-heading\"> Z\u00e4hlen der Bl\u00e4tter eines geschlossenen Ordners<\/h2><p> Jetzt haben wir hier Code, der auf die auf meinem Systemdesktop gespeicherte Arbeitsmappe verweist. Wenn ich diesen Code ausf\u00fchre, \u00f6ffnet er diese Arbeitsmappe im Hintergrund, z\u00e4hlt die darin enthaltenen Bl\u00e4tter und f\u00fcgt diese Anzahl dann der Zelle A1 hinzu.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Sub vba_count_sheets() Dim wb As Workbook Application.DisplayAlerts = False Set wb = Workbooks.Open(\"C:UsersDellDesktopsample-file.xlsx\") ThisWorkbook.Sheets(1).Range(\"A1\").Value _ = wb.Sheets.Count wb.Close SaveChanges:=True Application.DisplayAlerts = True End Sub<\/code><\/pre><p> Wir haben die Anzeigewarnungen zum \u00d6ffnen und Schlie\u00dfen der Datei im Backend deaktiviert.<\/p><div class=\"gb-container gb-container-4db6181c gb-container-mt\"> <a class=\"gb-button gb-button-c5897111 gb-button-text gb-button-mt-button\" href=\"https:\/\/exceladvisor.org\/de\/vba\/\">Was ist VBA<\/a><h2 class=\"gb-headline gb-headline-665e5f6b gb-headline-text gb-headline-mt-heading\"> Verwandte Tutorials <\/h2><div class=\"gb-grid-wrapper gb-grid-wrapper-a20baf7c gb-grid-wrapper-mt-grid\"><div class=\"gb-grid-column gb-grid-column-65ddf67e\"><div class=\"gb-container gb-container-65ddf67e\"><ul><li> <a href=\"https:\/\/exceladvisor.org\/de\/klares-blatt\/\">L\u00d6SCHEN Sie ein ganzes Blatt mit VBA in Excel<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/de\/kopierblatt\/\">Kopieren und verschieben Sie ein Blatt in Excel mit VBA<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/de\/ein-blatt-loschen-1\/\">L\u00d6SCHEN Sie ein BLATT mit VBA in Excel<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/de\/blatt-ausblenden\/\">Blenden Sie ein Blatt mit VBA in Excel ein und aus<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/de\/das-blatt-schutzen\/\">SCH\u00dcTZEN und UNSCHUTZ eines Blattes mit VBA in Excel<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/de\/blatt-umbenennen\/\">Benennen Sie ein Blatt mit VBA in Excel um<\/a> <\/li><\/ul><\/div><\/div><div class=\"gb-grid-column gb-grid-column-50067b57\"><div class=\"gb-container gb-container-50067b57\"><\/div><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"ub_ctt_via":"","footnotes":""},"class_list":["post-3269","page","type-page","status-publish","hentry"],"featured_image_src":null,"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Wie Z\u00c4HLEN Sie Bl\u00e4tter mit VBA in Excel?<\/title>\n<meta name=\"description\" content=\"In diesem Tutorial erfahren Sie, wie Sie Tabellen in Excel mithilfe von VBA auf verschiedene Weise z\u00e4hlen k\u00f6nnen.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/\" \/>\n<meta property=\"og:locale\" content=\"de_DE\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Wie Z\u00c4HLEN Sie Bl\u00e4tter mit VBA in Excel?\" \/>\n<meta property=\"og:description\" content=\"In diesem Tutorial erfahren Sie, wie Sie Tabellen in Excel mithilfe von VBA auf verschiedene Weise z\u00e4hlen k\u00f6nnen.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-03T13:05:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Gesch\u00e4tzte Lesezeit\" \/>\n\t<meta name=\"twitter:data1\" content=\"2\u00a0Minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/\",\"url\":\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/\",\"name\":\"Wie Z\u00c4HLEN Sie Bl\u00e4tter mit VBA in Excel?\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/de\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png\",\"datePublished\":\"2023-11-05T12:20:57+00:00\",\"dateModified\":\"2023-12-03T13:05:59+00:00\",\"description\":\"In diesem Tutorial erfahren Sie, wie Sie Tabellen in Excel mithilfe von VBA auf verschiedene Weise z\u00e4hlen k\u00f6nnen.\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#breadcrumb\"},\"inLanguage\":\"de\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"de\",\"@id\":\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#primaryimage\",\"url\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/exceladvisor.org\/de\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Wie z\u00e4hlt man bl\u00e4tter mit vba?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/exceladvisor.org\/de\/#website\",\"url\":\"https:\/\/exceladvisor.org\/de\/\",\"name\":\"Excel Advisor\",\"description\":\"Ihr Leitfaden zur Datendominanz\",\"publisher\":{\"@id\":\"https:\/\/exceladvisor.org\/de\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/exceladvisor.org\/de\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"de\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/exceladvisor.org\/de\/#organization\",\"name\":\"Excel Advisor\",\"url\":\"https:\/\/exceladvisor.org\/de\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"de\",\"@id\":\"https:\/\/exceladvisor.org\/de\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/exceladvisor.org\/de\/wp-content\/uploads\/2023\/11\/exceladvisor.org_.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/de\/wp-content\/uploads\/2023\/11\/exceladvisor.org_.png\",\"width\":105,\"height\":36,\"caption\":\"Excel Advisor\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/de\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Wie Z\u00c4HLEN Sie Bl\u00e4tter mit VBA in Excel?","description":"In diesem Tutorial erfahren Sie, wie Sie Tabellen in Excel mithilfe von VBA auf verschiedene Weise z\u00e4hlen k\u00f6nnen.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/","og_locale":"de_DE","og_type":"article","og_title":"Wie Z\u00c4HLEN Sie Bl\u00e4tter mit VBA in Excel?","og_description":"In diesem Tutorial erfahren Sie, wie Sie Tabellen in Excel mithilfe von VBA auf verschiedene Weise z\u00e4hlen k\u00f6nnen.","og_url":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/","og_site_name":"Excel Advisor","article_modified_time":"2023-12-03T13:05:59+00:00","og_image":[{"url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Gesch\u00e4tzte Lesezeit":"2\u00a0Minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/","url":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/","name":"Wie Z\u00c4HLEN Sie Bl\u00e4tter mit VBA in Excel?","isPartOf":{"@id":"https:\/\/exceladvisor.org\/de\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#primaryimage"},"thumbnailUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png","datePublished":"2023-11-05T12:20:57+00:00","dateModified":"2023-12-03T13:05:59+00:00","description":"In diesem Tutorial erfahren Sie, wie Sie Tabellen in Excel mithilfe von VBA auf verschiedene Weise z\u00e4hlen k\u00f6nnen.","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#breadcrumb"},"inLanguage":"de","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/"]}]},{"@type":"ImageObject","inLanguage":"de","@id":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#primaryimage","url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png","contentUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-feuilles-de-comptage-forment-le-classeur-actif.png"},{"@type":"BreadcrumbList","@id":"https:\/\/exceladvisor.org\/de\/zahle-die-blatter\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/exceladvisor.org\/de\/"},{"@type":"ListItem","position":2,"name":"Wie z\u00e4hlt man bl\u00e4tter mit vba?"}]},{"@type":"WebSite","@id":"https:\/\/exceladvisor.org\/de\/#website","url":"https:\/\/exceladvisor.org\/de\/","name":"Excel Advisor","description":"Ihr Leitfaden zur Datendominanz","publisher":{"@id":"https:\/\/exceladvisor.org\/de\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/exceladvisor.org\/de\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"de"},{"@type":"Organization","@id":"https:\/\/exceladvisor.org\/de\/#organization","name":"Excel Advisor","url":"https:\/\/exceladvisor.org\/de\/","logo":{"@type":"ImageObject","inLanguage":"de","@id":"https:\/\/exceladvisor.org\/de\/#\/schema\/logo\/image\/","url":"https:\/\/exceladvisor.org\/de\/wp-content\/uploads\/2023\/11\/exceladvisor.org_.png","contentUrl":"https:\/\/exceladvisor.org\/de\/wp-content\/uploads\/2023\/11\/exceladvisor.org_.png","width":105,"height":36,"caption":"Excel Advisor"},"image":{"@id":"https:\/\/exceladvisor.org\/de\/#\/schema\/logo\/image\/"}}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/exceladvisor.org\/de\/wp-json\/wp\/v2\/pages\/3269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceladvisor.org\/de\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/exceladvisor.org\/de\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/de\/wp-json\/wp\/v2\/comments?post=3269"}],"version-history":[{"count":1,"href":"https:\/\/exceladvisor.org\/de\/wp-json\/wp\/v2\/pages\/3269\/revisions"}],"predecessor-version":[{"id":4426,"href":"https:\/\/exceladvisor.org\/de\/wp-json\/wp\/v2\/pages\/3269\/revisions\/4426"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/de\/wp-json\/wp\/v2\/media?parent=3269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}