Frage
05.05.2009 11:17:09
sandra
ich habe folgendne Code erstellt der beim klicken auf einen button das makro auslöst innerhalb des blattes .,.ich möchte aber das es das für alle weiteren. von mir festgelegten Tabellenblätter übergreifend auch tut..wwas muss ich da tun ?
Private Sub CommandButton1_Click()
For Each blatt In Worksheets
Range("A1").Select
ActiveCell.FormulaR1C1 = "Projekt:"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Mitarbeiter:"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Schicht 1"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Schicht 2"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Schicht 3"
Range("B3:C3").Select
ActiveCell.FormulaR1C1 = "Name:"
Range("D3:E3").Select
ActiveCell.FormulaR1C1 = "Name:"
Range("F3").Select
ActiveCell.FormulaR1C1 = "Produktionstag:"
Range("F4").Select
ActiveCell.FormulaR1C1 = "Betriebsstunden:"
Range("I4").Select
ActiveCell.FormulaR1C1 = "h"
Range("G5").Select
ActiveCell.FormulaR1C1 = ""
Range("F5").Select
ActiveCell.FormulaR1C1 = "Packungsausbringung:"
Range("A7:B7").Select
ActiveCell.FormulaR1C1 = "Produktion"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Start"
Range("B8").Select
ActiveCell.FormulaR1C1 = "Stopp"
Range("C7:D7").Select
ActiveCell.FormulaR1C1 = "Zeiterfassung"
Range("C8").Select
ActiveCell.FormulaR1C1 = "Produktion"
Range("D8").Select
ActiveCell.FormulaR1C1 = "Stillstand"
Range("E7:E8").Select
ActiveCell.FormulaR1C1 = "Fehler" & Chr(10) & "Code"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("F7:F8").Select
ActiveCell.FormulaR1C1 = "Bau-gruppe"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("G7:G8").Select
ActiveCell.FormulaR1C1 = "Packungszähler"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("H7:H8").Select
ActiveCell.FormulaR1C1 = "Verlust Packung"
With ActiveCell.Characters(Start:=1, Length:=15).Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("I7").Select
ActiveCell.FormulaR1C1 = "Beschreibung Störung"
Range("J7:J8").Select
ActiveCell.FormulaR1C1 = "Montageaufwendungen"
With ActiveCell.Characters(Start:=1, Length:=19).Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("K7").Select
ActiveCell.FormulaR1C1 = "ausgetauschtes Bauteil"
Range("K8").Select
ActiveWindow.SmallScroll Down:=33
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A76").Select
ActiveCell.FormulaR1C1 = "Summe Zeit:"
Range("A77").Select
ActiveCell.FormulaR1C1 = "Summe Stillstand technisch:"
Range("A78").Select
ActiveCell.FormulaR1C1 = "Summe Stillstand organisatorisch:"
Range("A79").Select
ActiveCell.FormulaR1C1 = "Summe Rüstzeit:"
Range("E76").Select
ActiveCell.FormulaR1C1 = "Packungen prod.:"
Range("F77").Select
ActiveCell.FormulaR1C1 = ""
Range("E77").Select
ActiveCell.FormulaR1C1 = "Verlust Packungen: "
Range("E78").Select
ActiveCell.FormulaR1C1 = "Verlustanteil: "
Range("E79").Select
ActiveCell.FormulaR1C1 = "Summe Rollenwechsel:"
Range("I76").Select
ActiveCell.FormulaR1C1 = "Technischer Wirkungsgrad:"
Range("I77").Select
ActiveCell.FormulaR1C1 = "Mittlere Ausbringung [Pckg/h] / in %:"
Range("I78").Select
ActiveCell.FormulaR1C1 = "Betriebszeit:"
Range("A82:K85").Select
ActiveCell.FormulaR1C1 = "Kommentar:"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A82:K85").Select
ActiveCell.FormulaR1C1 = "Kommentar:"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("I89").Select
Next blatt
End Sub