AW: Ohne Schleifen
23.03.2005 16:37:40
Martin
Hallo Philipp,
hier noch die Alternative ohne Schleifen und Userform (ungetestet):
Sub tochterfirma2()
ws = ActiveSheet.Name
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If tochter = True Then
Worksheets(ws).Cells(2, 3).FormulaLocal = "=SÄUBERN('imp consult_name'!C" & a & ")"
Worksheets(ws).Cells(2, 15).FormulaLocal = "=SVERWEIS(C" & a & ";$Q$2:$R$3510;2;FALSCH)"
tochter = False
Else
Worksheets(ws).Cells(2, 3).FormulaLocal = "=WENN(ISTFEHLER(SVERWEIS(SÄUBERN('imp consult_name'!C" & a & ");'imp tochter'!$A$1:$B$3510;2;FALSCH));SÄUBERN('imp consult_name'!C" & a & ");SVERWEIS(SÄUBERN('imp consult_name'!C" & a & ");'imp tochter'!$A$1:$B$3510;2;FALSCH))"
Worksheets(ws).Cells(2, 15).FormulaLocal = "=wenn(istfehler(SVERWEIS(C" & a & ";$Q$2:$R$3510;2;FALSCH));SVERWEIS(C" & a & ";'imp tochter'!$B$2:$D$3510;3;FALSCH);SVERWEIS(C" & a & ";$Q$2:$R$3510;2;FALSCH))"
tochter = True
End If
Worksheets(ws).Cells(2, 3).Copy Destination:=Worksheets(ws).Range(Cells(3, 3), Cells(3510, 3))
Worksheets(ws).Cells(2, 15).Copy Destination:=Worksheets(ws).Range(Cells(3, 15), Cells(3510, 15))
'Pivots aktualisieren
Worksheets("Firmen").PivotTables("Firmen").RefreshTable
Worksheets("Firmen").PivotTables("PivotTable6").RefreshTable
Worksheets("Firmen").PivotTables("PivotTable5").RefreshTable
Worksheets("Firmen").PivotTables("PivotTable9").RefreshTable
Worksheets("Firmen").PivotTables("PivotTable3").RefreshTable
Worksheets("Firmen").Columns("J:J").Font.Bold = True
Worksheets("Firmen").Columns("A:A").Font.Bold = True
Worksheets("Firmen").Range("A7").Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
Worksheets("Firmen").Range("C7").Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
Worksheets("Firmen").Range("E7").Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
Worksheets("Firmen").Range("G7").Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
Worksheets("Firmen").Range("J6").Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Gruß
Martin Beck