hier also der Code
25.03.2010 16:01:53
Sunshine
Du wolltest es ja so.
Public Sub auto_open()
If ThisWorkbook.Name "StundennachweisHB_XX_Monat_JJJJ 1.xls" Then Exit Sub
Application.Visible = False
Call DatenKopieren
Call LeereEntfernen
Call KopieBerlinerKunden
Call AlphaSort
Call DoppelteEinträgeLöschen
Application.Visible = True
Public Sub DatenKopieren()
Application.DisplayAlerts = False
Sheets("Mandantenindexdatei").Select
Range(Columns(1), Columns(40)).Select
Selection.EntireColumn.Delete
Workbooks.Open Filename:= _
"K:\BC Actuarial Services\Hannover\Stundennachweise\Stundennachweis mit automatischer _
Aktualisierung der Mandanten\Mandanten Indexdatei.xls"
Cells.Select
Selection.Copy
ActiveWindow.Close
Sheets("Mandantenindexdatei").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range(Columns(8), Columns(40)).Select
Selection.EntireColumn.Delete
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub
ub LeereEntfernen()
Application.DisplayAlerts = True
Sheets("Mandantenindexdatei").Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=""
Cells.Select
Selection.Copy
Sheets("Hilfsd.").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Sheets("Mandantenindexdatei").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Sheets("Hilfsd.").Select
Range(Columns(1), Columns(40)).Select
Selection.EntireColumn.Delete
Sheets("Mandantenindexdatei").Activate
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub
Sub KopieBerlinerKunden()
Application.DisplayAlerts = False
Sheets("Mandantenindexdatei").Select
Cells(65000, 1).End(xlUp).Offset(1, 0).Select
ActiveCell.Offset(0, 2).Activate
Workbooks.Open Filename:= _
"K:\BC Actuarial Services\Hannover\Stundennachweise\Stundennachweis mit automatischer Aktualisierung der Mandanten\Berliner Mandanten.xls"
ActiveSheet.Range(Cells(1, 1), Cells(700, 5)).Select
Selection.Copy
ActiveWorkbook.Close
Windows("StundennachweisHB_XX_Monat_JJJJ 1.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub
Public Sub AlphaSort()
Sheets("Mandantenindexdatei").Select
Cells.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWorkbook.Save
End Sub
Public Sub DoppelteEinträgeLöschen()
Sheets("Mandantenindexdatei").Select
Cells.Select
Range("C1:G712").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Cells.Select
Selection.Copy
Sheets("Hilfsd.").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Sheets("Mandantenindexdatei").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Hilfsd.").Select
Range(Columns(1), Columns(40)).Select
Selection.EntireColumn.Delete
Sheets("Stundenerfassung").Select
Range("c250").End(xlUp).Offset(1, 0).Select
ActiveWorkbook.Save
End Sub