Re: Zeile formatieren
23.02.2003 01:56:30
Jörg
Hallo Peter,das Makro
strDatei1 = Application.ActiveWorkbook.Name
a = Worksheets.Count
Set ds = Application.FileSearch
With ds
.LookIn = Application.ActiveWorkbook.Path
.Filename = "evn*.slk"
If .Execute > 0 Then
strDatei = Application.GetOpenFilename("EVN-Dateien (*.slk), *.slk")
Else
MsgBox "Es wurden keine Dateien vom Typ [evn*.slk] gefunden."
End If
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open Filename:=strDatei
Sheets(1).Select
Sheets(1).Move After:=Workbooks(strDatei1).Sheets(a)
ActiveSheet.Name = "ENV"
Worksheets("ENV").Range("A6:M300").NumberFormat = "0"
Sheets("Tabelle1").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
strDatei1 = Application.ActiveWorkbook.Name
a = Worksheets.Count
Set ds = Application.FileSearch
With ds
.LookIn = Application.ActiveWorkbook.Path
.Filename = "pos*.slk"
If .Execute > 0 Then
strDatei = Application.GetOpenFilename("POS-Dateien (*.slk), *.slk")
Else
MsgBox "Es wurden keine Dateien vom Typ [pos*.slk] gefunden."
End If
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open Filename:=strDatei
Sheets(1).Select
Sheets(1).Move After:=Workbooks(strDatei1).Sheets(a)
ActiveSheet.Name = "POS"
Worksheets("POS").Range("A6:L30").NumberFormat = "General"
Sheets("Tabelle1").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
strDatei1 = Application.ActiveWorkbook.Name
a = Worksheets.Count
Set ds = Application.FileSearch
With ds
.LookIn = Application.ActiveWorkbook.Path
.Filename = "rechnung*.slk"
If .Execute > 0 Then
strDatei = Application.GetOpenFilename("Rechnung-Dateien (*.slk), *.slk")
Else
MsgBox "Es wurden keine Dateien vom Typ [rechnung*.slk] gefunden."
End If
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open Filename:=strDatei
Sheets(1).Select
Sheets(1).Move After:=Workbooks(strDatei1).Sheets(a)
ActiveSheet.Name = "Rechnung"
Worksheets("Rechnung").Range("A4:I10").NumberFormat = "General"
Sheets("Tabelle1").Select
Worksheets("Tabelle1").Range("A20:L320").NumberFormat = "General"
ActiveSheet.Name = Date
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveSheet.Shapes("CommandButton1").Select
Selection.Delete
Range("A20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C[1]"
Range("C20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C[1]"
Range("E20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("F20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("G20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("H20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("I20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("J20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("K20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("L20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("A20:L20").Select
Selection.AutoFill Destination:=Range("A20:L320"), Type:=xlFillDefault
Range("A20:L320").Select
ActiveWindow.SmallScroll Down:=-317
Range("A20").Select
Range("A20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C[1]"
Range("C20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C[1]"
Range("E20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("F20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("G20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("H20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("I20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("J20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("K20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("L20").Select
ActiveCell.FormulaR1C1 = "=ENV!R[-14]C"
Range("A20:L20").Select
Selection.AutoFill Destination:=Range("A20:L320"), Type:=xlFillDefault
Range("A20:L320").Select
ActiveWindow.SmallScroll Down:=-317
Range("A20").Select
Range("E8:E9").Select
ActiveCell.FormulaR1C1 = "=POS!R[-2]C[5]"
Range("E10:E11").Select
ActiveCell.FormulaR1C1 = "=POS!R[17]C[5]"
Range("I17").Select
ActiveCell.FormulaR1C1 = "=Rechnung!R[-13]C[-1]"
Range("I18").Select
End Sub
In der Tabelle Rechnung werte ich die Daten von den anderen Tabellen aus.