Makro alles als Tab. formatieren

Forumthread: Makro alles als Tab. formatieren

30.08.2017 09:54:38

Nils

Hallo zusammen,
ich arbeite häufig mit Systemdaten, die dann unformatiert auf diversen Tabellenblättern einer Exceldatei landen. Da ich diese Daten anschließend mit Power Pivot verknüpfen möchte, ist eine Vorabformatierung in das Tabellenformat notwendig bzw. sinnvoll.
Mit Makrorekorder bekomme ich es zwar hin, dass ein einzelner Export entsprechend automatisiert umgewandelt wird. Sobald aber eine Aktualisierung mit einer anderen Anzahl Zellen/Zeilen erfolgt, passt sich der Bezug nicht automatisch an. Meine Frage deshalb: Ist es möglich, mit einem Makro sämtliche befüllten Zellen aller Tabellenblätter einer Arbeitsmappe automatisch und umfassend in das Tabellenformat umzuwandeln? Ausgenommen davon soll allerdings immer das erste Tab. der Arbeitsmappe sein, weil hier Schaltflächen und Menüs untergebracht werden.
Besten Dank vorab und viele Grüße
Nils

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Makro alles als Tab. formatieren

30.08.2017 10:12:24

Michael
AW: Makro alles als Tab. formatieren

30.08.2017 10:44:31

nihil0815
Hm...

30.08.2017 16:34:37

Michael
AW: Hm...

30.08.2017 18:23:17

nihil0815
Gern, Danke für die Rückmeldung, owT

31.08.2017 07:53:56

Michael

AW: Makro alles als Tab. formatieren

30.08.2017 10:12:24

Michael

Hallo!
Folgender Code erstellt auf allen Blättern der Mappe, außer jenem dessen Name in der Konstante "AUSNAHME" angegeben ist, eine formatierte Tabelle (vom Typ "Mittel 2") aus dem verwendeten Blattbereich, unter Annahme einer Überschriftenzeile. Die jeweiligen formatierten Tabellen werden nach dem Muster "t_" & (zugehöriger) Tabellenblattname benannt.

Sub a()
Const AUSNAHME$ = "Tabelle1"
Dim Wb As Workbook: Set Wb = ThisWorkbook
Dim Ws As Worksheet
For Each Ws In Wb.Worksheets
If Ws.Name  AUSNAHME Then
With Ws
.ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "t_" & .Name
.ListObjects("t_" & .Name).TableStyle = "TableStyleMedium2"
End With
End If
Next Ws
Set Wb = Nothing: Set Ws = Nothing
End Sub

LG
Michael

AW: Makro alles als Tab. formatieren

30.08.2017 10:44:31

nihil0815

Hallo Michael,
vielen Dank erstmal. Ich mache hier offenbar etwas falsch:
https://www.herber.de/bbs/user/115884.xlsm
Kannst Du ersehen, wo der Fehler liegt?
Danke und viele Grüße
Nils

Hm...

30.08.2017 16:34:37

Michael

Hallo!
Ich mache hier offenbar etwas falsch
Naja. Zum einen hast Du Dein erstes Tabellenblatt umbenannt, da ich nicht davon ausgehe, dass dies wirklich "AUSNAHME" heißt - das ist nicht erforderlich bzw. hast Du mich/meinen Code da falsch verstanden. Es ist egal wie jenes Blatt, das übersprungen werden soll, benannt ist, und an welcher Position es sich in der Mappe befindet; wichtig ist nur, dass im Code (!) klar ist, um welches Blatt es sich handelt. D.h. hieße Dein Blatt, das übersprungen werden soll, bspw. "Lorem", dann muss im Code nur hier entsprechend angepasst werden:

Const AUSNAHME$ = "Lorem"

Was den Rest betrifft ist es so: Ich bin davon ausgegangen, dass Du einen "sauberen" Datenauszug hast - d.h. Tabellenblätter mit Rohdaten gefüllt, ohne Formatierungen oder benannte Bereiche etc. Das ist offenbar nicht der Fall, denn in Deinen relevanten Blättern sind überall benannte Bereiche definiert (Überbleibsel der Datenverbindung) - wenn sich das mit dem (formatierten) Tabellenbereich überschneidet, was hier immer der Fall sein wird, dann klappt das nicht. Dazu müsstest Du diese Bereiche zunächst in jedem relevanten Blatt löschen, bevor die formatierte Tabelle anlegst. Teste daher mal:

Sub b()
Const AUSNAHME$ = "AUSNAHME"
Dim Wb As Workbook: Set Wb = ThisWorkbook
Dim Ws As Worksheet, Qt As QueryTable
For Each Ws In Wb.Worksheets
If Ws.Name  AUSNAHME Then
With Ws
For Each Qt In .QueryTables: Qt.Delete: Next Qt
.ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "t_" & .Name
.ListObjects("t_" & .Name).TableStyle = "TableStyleMedium2"
End With
End If
Next Ws
Set Wb = Nothing: Set Ws = Nothing: Set Qt = Nothing
End Sub

Klappt?
LG
Michael

AW: Hm...

30.08.2017 18:23:17

nihil0815

Hallo Michael,
nochmals danke - funktioniert einwandfrei.
Was gäbe ich darum, wenn ich auch so tricksen könnte...
Viele Grüße
Nils

Gern, Danke für die Rückmeldung, owT

31.08.2017 07:53:56

Michael

Beliebteste Forumthreads (12 Monate)

Forumthread: Makro alles als Tab. formatieren

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread