Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Makro sehr langsam

Forumthread: Makro sehr langsam

Makro sehr langsam
20.03.2023 14:06:31
JuliChen

Hallo, ich habe ein Excel Makro, welches sehr viele Tabellenblätter in eine neue Datei kopiert, die Werte festsetzt und das Layout übernimmt. Das Makro läuft zwar, ist aber recht langsam. Kann man es irgendwie optimieren, damit es schneller läuft?

Sub Kopieren_und_Werte_einfuegen()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Dim wbSource As Workbook
Dim wbDest As Workbook
Dim ws As Worksheet

'Öffne die Quelldatei'
Set wbSource = ThisWorkbook

'Erstelle eine neue Arbeitsmappe'
Set wbDest = Workbooks.Add

'Gehe durch die Blätter und kopiere sie in die neue Arbeitsmappe'
For Each ws In wbSource.Worksheets
Select Case ws.Name
Case "Blatt1", "Blatt2", "Blatt3", "Blatt4", "Blatt5", "Blatt6", _
"Blatt7", "Blatt8", "Blatt9", "Blatt10", _
"Blatt11", "Blatt12", "Blatt13", "Blatt14", "Blatt15", _
"Blatt16", "Blatt17", "Blatt18", "Blatt19", _
"Blatt20", "Blatt21", "Blatt22", "Blatt23", "Blatt24", _
"Blatt25", "Blatt26", "Blatt27", "Blatt28", "Blatt29", "Blatt30"
ws.Copy After:=wbDest.Sheets(wbDest.Sheets.Count)
With wbDest.Sheets(wbDest.Sheets.Count)
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Cells(1).Select
End With
Case Else
'Überspringe Blätter, die nicht kopiert werden sollen'
End Select
Next ws

'Passe das Layout der Arbeitsmappe an'
With wbDest
.Sheets("Tabelle1").Delete
.SaveAs Filename:="Bericht 2023.xlsx", FileFormat:=xlOpenXMLWorkbook
End With

'Gebe eine Meldung aus, wenn das Makro abgeschlossen ist'
MsgBox "Das Makro wurde erfolgreich ausgeführt."

ApplicationScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub

Anzeige

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro sehr langsam
20.03.2023 14:21:10
onur
"Das Makro läuft zwar, ist aber recht langsam" -Sicher? Es hängt sich nicht auf???
Weisst du was das hier bedeutet:
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
Es bedeutet: Kopiere ALLE Zellen
Füge deren Werte in JEDE EINZELNE ZELLE des Blattes.


Anzeige
AW: Makro sehr langsam
20.03.2023 14:35:22
Rudi Maintaire
Hallo,
Es bedeutet: Kopiere ALLE Zellen
Füge deren Werte in JEDE EINZELNE ZELLE des Blattes.

na und?
Viel kritischer ist in meinen Augen, dass die Blätter nach und nach kopiert werden. Falls es Bezüge zwischen ihnen gibt, müssen die aufwändig angepasst werden.

Gruß
Rudi


Anzeige
AW: Makro sehr langsam
20.03.2023 14:36:15
onur
Dann TESTE es mal bei DIR !


AW: Makro sehr langsam
20.03.2023 14:38:29
onur
Ich meine, nur diese beiden Zeilen...


AW: Makro sehr langsam
20.03.2023 14:47:29
Rudi Maintaire
Hallo,
Sub aaa()
Dim t
t = Timer
With Cells
  .Copy
  .PasteSpecial xlPasteValues
End With
Debug.Print (Timer - t) * 1000
End Sub
Ergebnis: 54,6875 = 0,0546875 Sekunden.

Gruß
Rudi


Anzeige
AW: Makro sehr langsam
20.03.2023 14:49:49
onur
ICH spreche von
CELLS.Pastespecial xlPasteValues


was steht denn in meinem Code?
20.03.2023 14:58:30
Rudi Maintaire
genau das.
Versuchst du, mich zu belehren?

Gruß
Rudi

(Ich weiß, dass ich nicht unfehlbar bin)


Anzeige
AW: Makro sehr langsam
20.03.2023 14:45:50
onur
Oder teste nur das:
    Range("A1").Copy
    Cells.PasteSpecial xlPasteValues


das ist was ganz anderes
20.03.2023 14:56:44
Rudi Maintaire
du schreibst Blödsinn.
Eine Zelle kopieren und in alle Zellen einfügen ist was anderes als alle Zellen kopieren und in alle einfügen. Denn dann bleiben leere Zelle auch leer.

Gruß
Rudi


Anzeige
AW: das ist was ganz anderes
20.03.2023 15:02:42
onur
Sorry, Rudi - du hattest Recht. ICH hätte es von Anfang an so testen sollen. Da ich den Verdacht hatte, dass es in die Hose gehen könnte, habe ich es erstmal mit Range("A1").Copy getestet und meine Befürchtung wurde natürlich bestätigt.....
Sorry!


Anzeige
AW: Makro sehr langsam
20.03.2023 15:16:58
Klexy
Mit
xlPasteValues
werden doch alle Bezüge gekillt. Das sollte kein Problem sein.


AW: Makro sehr langsam
20.03.2023 15:22:59
Rudi Maintaire
Hallo,
ja. Aber erst nachdem das Blatt kopiert wurde.
Wenn du ein Blatt aus einer Mappe kopierst, das Bezüge auf andere Blätter der Mappe hat, müssen diese ja angepasst werden.
Aus =Tabelle1!A1 wird ='[Datei1.xlsx]Tabelle1'!A1
Schlimmer ist es mit komplexeren Formeln. Und das dauert unter Umständen.

Gruß
Rudi


Anzeige
AW: Makro sehr langsam
21.03.2023 14:40:50
Klexy
Das stimmt. Ich wollte aber keine Annahmen treffen ohne ein konkretes Muster.
Ich würde es so machen, wie es Daniel vorgeschlagen hat. Werte, Formate, Spaltenbreiten. Ist eine sichere Bank.


AW: Makro sehr langsam
20.03.2023 15:23:10
onur
Es geht nicht um die Bezüge, sondern um die Zellanzahl.
DAS geht in die Hose (das war es auch, was ich getestet hatte)
Range("A1").Copy
Cells.PasteSpecial xlPasteValues
Das hier aber klappt seltsamerweise (hätte ich mal stattdessen testen sollen)
Cells.Copy
Cells.PasteSpecial xlPasteValues
Unlogisch, aber wahr.


Anzeige
AW: Makro sehr langsam
20.03.2023 15:22:12
Klexy
Es bedeutet: Entferne im kompletten Blatt alle Formelbezüge.
Das ist hier ein sehr cleverer Schachzug, wenn in der Zieldatei nur die Werte relevant sind und nicht die Rechnungswege.
Wenn natürlich in den bedingten Formatierungen Bezüge über das aktuelle Tabellenblatt hinaus sind, hat man weiterhin ein Problem.
Und das könnte die Geschichte verlangsamen.


Anzeige
AW: Makro sehr langsam
20.03.2023 14:28:52
JuliChen
Ja es läuft wirklich durch ohne sich aufzuhängen...aber eben sehr langsam. Ich dachte mir schon, dass das Problem ist. Bin aber leider kein VBA Spezi. Daher habe ich gehofft, Vorschläge zu bekommen wie ich das verbessern kann.


AW: Makro sehr langsam
20.03.2023 14:32:21
onur
Muss heissen
.Range("A1").PasteSpecial xlPasteValues


Anzeige
AW: Makro sehr langsam
20.03.2023 15:25:17
Klexy
Hast du eventuell verhundertfachte bedingte Formatierungen in deiner Datei? Das ist ein Excel-Bug, der die Sache ordentlich verlangsamen kann.
Am besrten hängst du die Datei mal hier an.
Wenn dir die Daten zu heikel sind, mach aus jeder 1,2,3 eine 4 und aus jeder 5,6,7 eine 8 und aus jeder 9 eine 0.


Anzeige
AW: Makro sehr langsam
20.03.2023 14:29:38
Rudi Maintaire
Hallo,
versuch mal:
Sub Kopieren_und_Werte_einfuegen()
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Application.Calculation = xlCalculationManual
  
  Dim wbSource As Workbook
  Dim wbDest As Workbook
  Dim ws As Worksheet
  
  'Öffne die Quelldatei'
  Set wbSource = ThisWorkbook
  
  'Erstelle eine neue Arbeitsmappe'
  
  'Gehe durch die Blätter und kopiere sie in die neue Arbeitsmappe'
  wbSource.Sheets(Array("Blatt1", "Blatt2", "Blatt3", "Blatt4", "Blatt5", "Blatt6", "Blatt7", "Blatt8", _
    "Blatt9", "Blatt10", "Blatt11", "Blatt12", "Blatt13", "Blatt14", "Blatt15", "Blatt16", "Blatt17", _
    "Blatt18", "Blatt19", "Blatt20", "Blatt21", "Blatt22", "Blatt23", "Blatt24", "Blatt25", "Blatt26", _
    "Blatt27", "Blatt28", "Blatt29", "Blatt30")).Copy
    
  Set wbDest = ActiveWorkbook
  For Each ws In wbDest.Worksheets
    With ws
      .Cells.Copy
      .Cells.PasteSpecial xlPasteValues
      Application.CutCopyMode = False
    End With
  Next ws
  
  'Passe das Layout der Arbeitsmappe an'
  wbDest.SaveAs fileName:="Bericht 2023", FileFormat:=xlOpenXMLWorkbook
  
  'Gebe eine Meldung aus, wenn das Makro abgeschlossen ist'
  MsgBox "Das Makro wurde erfolgreich ausgeführt."
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  Application.Calculation = xlCalculationAutomatic
  
End Sub
Gruß
Rudi


Anzeige
AW: Makro sehr langsam
20.03.2023 14:30:19
Daniel
Hi
das Sheets.Copy ist sehr langsam, weil Excel hier alles zum Sheet kopiert, dh auch die Makros und Druckereinstellungen.
Die Druckereinstellungen sind hier die Bremse, weil Excel sich dann erstmal mit dem Drucker "unterhält"

um das zu beschleunigen gibts zwei Wege:

a) kein Sheet.Copy durchführen, sondern das Blatt in der Zieldatei leer anlegen und dann die Inhalte kopieren und einfügen.
Ist sinnvoll, wenn man nur Daten übernehmen will, aber keine Makros, Grafikobjekte und nicht die Druckereinstellungen

b) Excel "verbieten", mit dem Drucker zu sprechen:
Application.PrintCommunication = False
...
Sheets.copy
...
Application.PrintCommunication = True
Gruß Daniel


Anzeige
AW: Makro sehr langsam
20.03.2023 15:01:38
Daniel
probier mal, ob das schneller ist:
kopiert werden nur Tabelleninhalte und Formatierungen.
Sub test()
Dim x As Long, y As Long
Dim wbSource As Workbook
Dim wbDest As Workbook
Dim shSource As Worksheet

Set wbSource = ThisWorkbook

For Each shSource In wbSource.Worksheets
    If shSource.Name Like "Blatt*" Then x = x + 1
Next
If x = 0 Then Exit Sub

y = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = x
Set wbDest = Workbooks.Add
Application.SheetsInNewWorkbook = y

x = 0
For Each shSource In wbSource.Worksheets
    If shSource.Name Like "Blatt*" Then
        x = x + 1
        shSource.UsedRange.Copy
        With wbDest.Sheets(x)
            .Name = shSource.Name
            With .Cells(1, 1)
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                .PasteSpecial xlPasteColumnWidths
            End With
        End With
    End If
Next
    
    
End Sub

Anzeige
;

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige