Hallo Leute,
nachfolgend ein selbstgemachtes Riesenmakro vom Newbie thomas. Habe schon ein paar heisse Tipps zur Verbesserung eingebaut. Momentan brauch die Ausführung noch 21 Sekunden. Hat jemand noch Tipps wie's noch schneller geht? Die mühsame Handarbeit um die Tipps einzubauen kann ich dann selbst machen.
Besten Dank!
thomas
Private Sub CheckBox2_Click() If CheckBox2.Value = True Then Sheets("Start").ComboBox1.ListIndex = 3 'damit, egal was User wählt, für Bsp 4 Szen und 3 Prod. gewählt werden Sheets("Start").ComboBox2.ListIndex = 1 Application.ScreenUpdating = False Dim ws As Worksheet If Sheets("Start").OptionButton4 = True And Sheets("Start").OptionButton6 = True Then Set ws = Sheets("Beispieldaten") With Sheets("Erfolgssens.-Analyse BM") 'Für BM-Blatt .Range("c10") = ws.Range("a1") .Range("c12") = ws.Range("a2") .Range("f14") = ws.Range("a3") .Range("h14") = ws.Range("a4") .Range("f16") = ws.Range("b3") .Range("h16") = ws.Range("b4") .Range("c68") = ws.Range("a20") .Range("c101") = ws.Range("a20") .Range("c134") = ws.Range("a20") .Range("f72") = ws.Range("a23") .Range("f105") = ws.Range("a24") ws.Range("a5:g6").Copy .Range("f47:l48").PasteSpecial Paste:=xlPasteValues .Range("f80:l81").PasteSpecial Paste:=xlPasteValues .Range("f113:l114").PasteSpecial Paste:=xlPasteValues ws.Range("a7:a12").Copy .Range("c52:c57").PasteSpecial Paste:=xlPasteValues .Range("c85:c90").PasteSpecial Paste:=xlPasteValues .Range("c118:c123").PasteSpecial Paste:=xlPasteValues ws.Range("a13:a19").Copy .Range("c60:c66").PasteSpecial Paste:=xlPasteValues .Range("c93:c99").PasteSpecial Paste:=xlPasteValues .Range("c126:c132").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End With End If If Sheets("Start").OptionButton4 = True And Sheets("Start").OptionButton7 = True Then Set ws = Sheets("Beispieldaten") With Sheets("Erfolgssens.-Analyse BW") 'Für BM-Blatt .Range("d10") = ws.Range("a26") .Range("d14") = ws.Range("a27") .Range("d16") = ws.Range("a28") .Range("d18") = ws.Range("a29") .Range("d20") = ws.Range("a30") .Range("d63") = ws.Range("a45") .Range("d91") = ws.Range("a45") .Range("d119") = ws.Range("a45") .Range("d67") = ws.Range("a48") .Range("d95") = ws.Range("a49") ws.Range("a31:g31").Copy .Range("d45:j45").PasteSpecial Paste:=xlPasteValues .Range("d73:j73").PasteSpecial Paste:=xlPasteValues .Range("d101:j101").PasteSpecial Paste:=xlPasteValues ws.Range("a32:a37").Copy .Range("d47:d52").PasteSpecial Paste:=xlPasteValues .Range("d75:d80").PasteSpecial Paste:=xlPasteValues .Range("d103:d108").PasteSpecial Paste:=xlPasteValues ws.Range("a38:a44").Copy .Range("d55:d61").PasteSpecial Paste:=xlPasteValues .Range("d83:d89").PasteSpecial Paste:=xlPasteValues .Range("d111:d117").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End With End If If Sheets("Start").OptionButton5 = True And Sheets("Start").OptionButton6 = True Then Set ws = Sheets("Beispieldaten") With Sheets("Erfolgssens.-Analyse BSM") 'Für BM-Blatt .Range("aa78") = ws.Range("a81") .Range("aa131") = ws.Range("a81") .Range("aa184") = ws.Range("a81") .Range("aa88") = ws.Range("a87") .Range("aa141") = ws.Range("a87") .Range("aa194") = ws.Range("a87") .Range("aa92") = ws.Range("a48") .Range("aa145") = ws.Range("a49") ws.Range("a51:e67").Copy .Range("c12:g28").PasteSpecial Paste:=xlPasteValues ws.Range("a68:e74").Copy .Range("c52:g58").PasteSpecial Paste:=xlPasteValues .Range("c105:g111").PasteSpecial Paste:=xlPasteValues .Range("c158:g164").PasteSpecial Paste:=xlPasteValues ws.Range("a75:e80").Copy .Range("c77:g82").PasteSpecial Paste:=xlPasteValues .Range("c130:g135").PasteSpecial Paste:=xlPasteValues .Range("c183:g188").PasteSpecial Paste:=xlPasteValues ws.Range("a82:a83").Copy .Range("aa80:aa81").PasteSpecial Paste:=xlPasteValues .Range("aa133:aa134").PasteSpecial Paste:=xlPasteValues .Range("aa186:aa187").PasteSpecial Paste:=xlPasteValues ws.Range("a84:a86").Copy .Range("aa83:aa85").PasteSpecial Paste:=xlPasteValues .Range("aa136:aa138").PasteSpecial Paste:=xlPasteValues .Range("aa189:aa191").PasteSpecial Paste:=xlPasteValues ws.Range("f68:j69").Copy .Range("af52:aj53").PasteSpecial Paste:=xlPasteValues .Range("af105:aj106").PasteSpecial Paste:=xlPasteValues .Range("af158:aj159").PasteSpecial Paste:=xlPasteValues ws.Range("k68:o69").Copy .Range("bi52:bm53").PasteSpecial Paste:=xlPasteValues .Range("bi105:bm106").PasteSpecial Paste:=xlPasteValues .Range("bi158:bm159").PasteSpecial Paste:=xlPasteValues ws.Range("p68:t69").Copy .Range("cl52:cp53").PasteSpecial Paste:=xlPasteValues .Range("cl105:cp106").PasteSpecial Paste:=xlPasteValues .Range("cl158:cp159").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End With End If If Sheets("Start").OptionButton5 = True And Sheets("Start").OptionButton7 = True Then Set ws = Sheets("Beispieldaten") With Sheets("Erfolgssens.-Analyse BSW") 'Für BM-Blatt .Range("aa62") = ws.Range("a110") .Range("aa99") = ws.Range("a110") .Range("aa136") = ws.Range("a110") .Range("aa72") = ws.Range("a116") .Range("aa109") = ws.Range("a116") .Range("aa146") = ws.Range("a116") .Range("aa76") = ws.Range("a97") .Range("aa113") = ws.Range("a99") ws.Range("a89:e99").Copy .Range("c13:g23").PasteSpecial Paste:=xlPasteValues ws.Range("a100:e106").Copy .Range("c48:g54").PasteSpecial Paste:=xlPasteValues .Range("c85:g91").PasteSpecial Paste:=xlPasteValues .Range("c122:g128").PasteSpecial Paste:=xlPasteValues ws.Range("a107:e109").Copy .Range("c61:g63").PasteSpecial Paste:=xlPasteValues .Range("c98:g100").PasteSpecial Paste:=xlPasteValues .Range("c135:g137").PasteSpecial Paste:=xlPasteValues ws.Range("a111:a112").Copy .Range("aa64:aa65").PasteSpecial Paste:=xlPasteValues .Range("aa101:aa102").PasteSpecial Paste:=xlPasteValues .Range("aa138:aa139").PasteSpecial Paste:=xlPasteValues ws.Range("a113:a115").Copy .Range("aa67:aa69").PasteSpecial Paste:=xlPasteValues .Range("aa104:aa106").PasteSpecial Paste:=xlPasteValues .Range("aa141:aa143").PasteSpecial Paste:=xlPasteValues ws.Range("f102:j102").Copy .Range("af50:aj50").PasteSpecial Paste:=xlPasteValues .Range("af87:aj87").PasteSpecial Paste:=xlPasteValues .Range("af124:aj124").PasteSpecial Paste:=xlPasteValues ws.Range("k102:o102").Copy .Range("bi50:bm50").PasteSpecial Paste:=xlPasteValues .Range("bi87:bm87").PasteSpecial Paste:=xlPasteValues .Range("bi124:bm124").PasteSpecial Paste:=xlPasteValues ws.Range("p102:t102").Copy .Range("cl50:cp50").PasteSpecial Paste:=xlPasteValues .Range("cl87:cp87").PasteSpecial Paste:=xlPasteValues .Range("cl124:cp124").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End With End If Else If Sheets("Start").OptionButton4 = True And Sheets("Start").OptionButton6 = True Then With Sheets("Erfolgssens.-Analyse BM") 'Für BM-Blatt .Range("c10, c12, f14, h14, f16, h16, c68, c101, c134, f72, f105").ClearContents .Range("f47:l48, f80:l81, f113:l114, c52:c57, c85:c90, c118:c123, c60:c66, c93:c99, c126:c132").ClearContents End With End If If Sheets("Start").OptionButton4 = True And Sheets("Start").OptionButton7 = True Then With Sheets("Erfolgssens.-Analyse BW") 'Für BM-Blatt .Range("d10, d14, d16, d18, d20, d63, d91, d119, d67, d95").ClearContents .Range("d45:j45, d73:j73, d101:j101, d47:d52, d75:d80, d103:d108, d55:d61, d83:d89, d111:d117").ClearContents End With End If If Sheets("Start").OptionButton5 = True And Sheets("Start").OptionButton6 = True Then With Sheets("Erfolgssens.-Analyse BSM") 'Für BM-Blatt .Range("aa78, aa131, aa184, aa88, aa141, aa194, aa92, aa145").ClearContents .Range("c12:g28,c52:g58,c105:g111,c158:g164,c77:g82,c130:g135,c183:g188").ClearContents .Range("aa80:aa81,aa133:aa134,aa186:aa187,aa83:aa85,aa136:aa138,aa189:aa191").ClearContents .Range("af52:aj53,af105:aj106,af158:aj159,bi52:bm53,bi105:bm106,bi158:bm159,cl52:cp53,cl105:cp106,cl158:cp159").ClearContents End With End If If Sheets("Start").OptionButton5 = True And Sheets("Start").OptionButton7 = True Then With Sheets("Erfolgssens.-Analyse BSW") 'Für BM-Blatt .Range("aa62,aa99,aa136,aa72,aa109,aa146,aa76,aa113,c13:g23,c48:g54,c85:g91,c122:g128,c61:g63,c98:g100,c135:g137").ClearContents .Range("aa64:aa65,aa101:aa102,aa138:aa139,aa67:aa69,aa104:aa106,aa141:aa143,af50:aj50,af87:aj87,af124:aj124").ClearContents .Range("bi50:bm50,bi87:bm87,bi124:bm124,cl50:cp50,cl87:cp87,cl124:cp124").ClearContents End With End If End If Application.ScreenUpdating = True End Sub