Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

Forumthread: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

10.07.2024 17:04:23

jakobh1

Hallo zusammen,

ich erzeuge eine Exceldatei mit unterschiedlich vielen Reitern. Ich möchte in allen Reitern die Spalten G, H, I und K in Werte umwandeln, da sich in diesen als Text formatierte Zahlen befinden. Die Umwandlung soll in allen Reitern vorgenommen werden, bis auf Reiter 1.

Der Reiter 1 ist immer als "Übersicht" benannt. Die folgenden Reiter sind immer fortlaufend nummeriert (von 1 bis xxx), wobei es manchmal 20 Reiter sind und mal 300. Wie kann ich das mit VBA umsetzen?

Mein aktueller Code lautet:

Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("I:I").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

10.07.2024 17:26:24

schauan

Hallöchen,

da der code unvollständig ist mal nur ein Tipp, im Prinip:

If ActiveSheet.Name = "Übersicht" Then exit sub

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

11.07.2024 12:17:50

GerdL

Hallo Jakob!
Ein ungetester Versuch, es ohne Selection auf den Punkt zu bringen.

Sub test456()



Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets

With wks

If ActiveSheet.Name = "Übersicht" Then Exit Sub



.Columns("G:G").TextToColumns Destination:=.Range("G1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



.Columns("G:G").TextToColumns Destination:=.Range("H1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



.Columns("I:I").TextToColumns Destination:=.Range("I1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



.Columns("K:K").TextToColumns Destination:=.Range("K1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True





End With

Next wks

End Sub

Gruß Gerd

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

11.07.2024 16:45:53

jakobh1

Hallo Gerd, hallo Peter,

vielen lieben Dank! Beide Lösungen funktionieren.

Was ich noch nicht verstehe...wozu ist der Punkt vor Columns bzw. Range da? Warum benötige ich diesen Punkt nicht auch bei den anderen "Einstellungen"? Also z.B. DataType:=.xl Delimited?
Ich hatte den Code über die Makroaufzeichnung erzeugt. Dort fehlten die Punkte. Warum konnte ich diesen trotzdem auslösen? Anbei nochmal der Code von der Makroaufzeichnung:

Sub Makro1()
'
' Makro1 Makro
'

'
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

11.07.2024 17:43:54

daniel

Hi
der Punkt ist immer ein Platzhalter und dient einfach nur dazu, Code kürzer und übersichtlicher zu schreiben.
immer wenn du den Punkt irgendwo am Anfang eines Ausdrucks siehst, dann muss irgendwo vorher eine Zeile mit WITH Objekt stehen.
gedanklich musst du dann immer dieses Objekt dort einsetzen, wo der Ausdruck mit dem Punkt beginnt.

du machst das ja implizit schon über Select/Selection, denn wenn du das TextINSpalten mit der Spalte G ausführen willst, dann selektierst du sie zuerst und wendest dann das TextInSpalten auf diese an:

Columns("G:G").Select

Selection.TextToColumns

Eigentlich ist damit das gemeint, das Selection steht nur da, weil der Recorder das so aufzeichnet, da wir mit der Maus so arbeiten müssen, denn wir können immer nur eine Sache auswählen, daher wählen wir zuerst das Objekt aus, mit dem wir etwas machen möchten und danach erst den Befehl, den wir auf das Objekt anwenden wollen.
In VBA kann man das in einem Schritt machen:

Columns("G:G").TextToColumns

Punkt und WITH arbeiten jetzt so ähnlich, dh man definiert sich mit dem WITH ein Objekt und wendet dann darauf die Befehle an:

With Columns("G:G")

    .TextToColumns

der Vorteil ist, dass man sich hier Schreibarbeit spart, wenn man dieses Objekt nicht nur einmal verwenden möchte, sondern mehrere Aktionen mit diesem Objekt ausführen will, beispielsweise sowas:

With Columns("G:G")

    .TextToColumns

    .Font.Size = 10

    .ColumnWidth = 20

    .Interior.Color = vbRed

End with

das ist kürzer und übersichtlicher als

    Columns("G:G").TextToColumns

    Columns("G:G").Font.Size = 10

    Columns("G:G").ColumnWidth = 20

    Columns("G:G").Interior.Color = vbRed

und wenn du mal irgendwann aus Spalte G die Spalten F machen willst, musst du den code nur einmal ändern und nicht an mehreren Stellen, das senkt das Risiko, dass du eine Stelle vergisst

insbesondere beim TextToColumns hat das einen interessanten Effekt:
man muss ja die Zielzelle angeben, ab welcher die neuen Texte eingefügt werden:

Columns("G:G").Select

Selection.TextToColumns Destination:=Range("G1"),

wenn man dieses Range("G1") in Abhängigkeit von Spalte G beschreibt, so ist G1 immer die erste Spalte und erste Zeile der Spalte G, dh Columns("G:G").Cells(1, 1) ist das gleiche wie Range("G1").
wenn man das jetzt mit der WITH-Klammer schreibt:

With Columns("G:G")

    .TextToColumns Destinatinon:= .Cells(1, 1)

so ist das das gleiche wie

Columns("G:G").TextToColumns Destination:=Range("G1")

,
aber wenn man jetzt das ganze nach Spalte F verschieben will, muss man eben nur in der WITH-Klammer die Änderung durchführen und nicht bei der Destination.

Gruß Daniel

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

12.07.2024 09:25:55

jakobh1

Ach super. Vielen Dank für die ausführliche Erläuterung :)

AW: Korrektur

11.07.2024 12:27:17

GerdL

Sub test789()



Dim wks As Worksheet



For Each wks In ActiveWorkbook.Worksheets

With wks

If .Name > "Übersicht" Then 'name anpassen



.Columns("G:G").TextToColumns Destination:=.Range("G1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



.Columns("G:G").TextToColumns Destination:=.Range("H1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



.Columns("I:I").TextToColumns Destination:=.Range("I1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



.Columns("K:K").TextToColumns Destination:=.Range("K1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



End If

End With

Next wks

End Sub





End Sub

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

11.07.2024 11:42:28

jakobh1

Danke dir für deine Antwort. Ich habe es jetzt anders gelöst, aber habe noch ein Problem. Weiß jemand was ich falsch mache?

Den Code findet ihr unten. Das Makro soll in allen Tabellenblättern ausgeführt werden, die als Sheet* bezeichnet sind. In meiner Datei sind es z.B. Sheet1 bis Sheet120. Wenn ich nun das Makro ausführe, dann wird der Code nur im aktuell ausgewählten Tabellenblatt ausgeführt und es schaut so aus, als würde der Code aber nicht nur einmal ausgeführt werden, sondern 120 mal. Was muss ich ändern, damit nicht nur das eine Tabellenblatt bearbeitet wird, sondern alle 120?

Sub test123()

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
If wks.Name Like "Sheet*" Then

Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("I:I").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

End If
End With
Next wks
End Sub

Hier ein Beispiel für einen anderen Code. Dieser funktioniert einwandfrei. In allen Tabellenblättern mit dem Namen Sheet* wird die Zelle D1 gelb gefärbt.

Sub testmitanderemcode()

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
If wks.Name Like "Sheet*" Then

.Range("D1").Interior.Color = vbYellow

End If
End With

Next wks
End Sub

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

11.07.2024 11:48:31

peter

Hallo

Du hast den Punkt . vergessen



With wks

  If .Name Like "Sheet*" Then



 .Columns("G:G").Select

Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True

Peter

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

11.07.2024 11:58:52

jakobh1

Wenn ich den Punkt hinzunehme, kommt die Nachricht:

Laufzeitfehler 1004: Die Select-Methode des Range-Objektes konnte nicht ausgeführt werden.

AW: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

11.07.2024 12:16:49

peter

Hallo

Es fehlt auch noch ein Punkt bei Destination

Destination:=.Range("G1")

Peter

Beliebteste Forumthreads (12 Monate)

Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

Forumthread: Makro in Arbeitsblättern anwenden(mit Ausnahme Blatt 1)

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread