AW: Liste auf mehrere Arbeitsblätter aufteilen
06.07.2010 15:17:07
Hei.Fisch
Hallo René,
ich habe nicht genau das, was Du brauchst, jedoch ähnlich.
Das Makro "CopyWithValue", bei dem mich Franz sehr unterstützt hat, macht im Prinzip genau das, was Du suchst, nur noch nicht mit den Versionen berücksichtigt. Es legt im gleichen Ordner der Ursprungsdatei neue Arbeitsmappen mit vorgegebenen Namen an oder befüllt bereits vorhandene Dateien mit neuen Daten.
Im letzten Abschnitt kannst Du im VBA-Code eingeben, 1. nach welchem (Text)-Wert, 2. in welcher Spalte gesucht werden soll, 3. wie die Zieldatei benannt werden soll und 4. optional noch ein Passwort für die Zieldatei vergeben.
Ist voraussichtlich nicht die endgültige Lösung für Deine Aufgabe, jedoch ein brauchbarer Ansatz.
Viele Grüße,
Heidrun
Option Explicit
Private Sub SaveWithValues(testValue As String, column As String, _
sheetName As String, Optional sPassword As String = "")
Dim rootSheet As Object
Dim newSheet As Object
Dim workRange As Range
Dim destinationRange As Range
Dim currentRow As Range
Dim strValue As String
Dim counter As Integer, Spalte
Dim row As String
Dim wbZiel As Workbook, sWorkbook As String
Application.StatusBar = "Bearbeite Datei für """ & testValue & """"
counter = 1 'Zeile mit Spaltentiteln im Zielblatt - ggf. anpassen
Set rootSheet = ActiveSheet
Set workRange = rootSheet.UsedRange
On Error GoTo Fehler
'Name der Datei für den Vertriebs-Ing.
sWorkbook = ActiveWorkbook.Path & Application.PathSeparator & sheetName & ".xls"
'Prüfen ob Datei vorhanden
If Dir(sWorkbook) "" Then
'Datei ist schon vorhanden
Set wbZiel = Workbooks.Open(Filename:=sWorkbook, Password:=sPassword, _
IgnoreReadonlyRecommended:=True)
Set newSheet = wbZiel.Worksheets(sheetName)
'Altdaten unterhalb der Spaltentitel löschen
With newSheet
If .Cells.SpecialCells(xlCellTypeLastCell).row > counter Then
.Range(.Rows(counter + 1), _
.Rows(.Cells.SpecialCells(xlCellTypeLastCell).row)).Clear
End If
End With
Else
'Neue Datei mit einem Tabellenblatt anlegen
Set wbZiel = Workbooks.Add(Template:=xlWBATWorksheet)
Set newSheet = wbZiel.Worksheets(1)
'Datei im älteren Format speichern
If Val(Left(Application.Version, 2)) >= 12 Then
'Excel 2007 und neuer
wbZiel.SaveAs Filename:=sWorkbook, FileFormat:=56, _
Password:=sPassword '56 = xlExcel8
Else
'Ältere Excel-Versionen
wbZiel.SaveAs Filename:=sWorkbook, FileFormat:=-4143, _
Password:=sPassword ' -4143 = xlWorkbookNormal
End If
NeuesBlatt:
newSheet.Name = sheetName
With newSheet
'Standard-Fontgröße festlegen
wbZiel.Styles("Standard").Font.Size = 10
'Spaltenbreiten übernehmen
For Spalte = 1 To rootSheet.Cells.SpecialCells(xlCellTypeLastCell).column
.Columns(Spalte).ColumnWidth = .Columns(Spalte).ColumnWidth
Next
End With
'Titelzeile kopieren
rootSheet.Rows(1).Copy newSheet.Rows(counter)
'Fenster fixieren
Range("D2").Select
ActiveWindow.FreezePanes = True
End If
newSheet.Activate
'Daten übertragen
For Each currentRow In workRange.Rows
strValue = CStr(currentRow.Range(column & 1).Text)
If strValue = testValue Then
counter = counter + 1
Set destinationRange = newSheet.Rows(counter)
currentRow.Copy destinationRange
End If
Next
wbZiel.Close savechanges:=True
Fehler:
With Err
Select Case .Number
Case 0 ' Alles OK
Case 9 'Blatt fehlt
Application.ScreenUpdating = True
MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description & vbLf & vbLf _
& "Fehlendes Tabellenblatt """ & sheetName & """ wird angelegt", _
vbInformation + vbOKOnly, "Vertreter-Arbeitsmappen erstellen/ausfüllen"
Application.ScreenUpdating = False
Set newSheet = wbZiel.Worksheets.Add
Resume NeuesBlatt
Case Else
Application.ScreenUpdating = True
MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description, _
vbInformation + vbOKOnly, "Vertreter-Arbeitsmappen erstellen/ausfüllen"
Application.ScreenUpdating = False
End Select
End With
Application.StatusBar = False
End Sub
Sub KB_vereinzeln_Arbeitsmappen()
Application.ScreenUpdating = False
'SaveWithValues testValue:="NameIng", column:="F", sheetName:="NameIng", _
sPassword:= "AXBc120"
SaveWithValues "Peter", "F", "Peter", "PW_Test"
SaveWithValues "Steffen", "F", "Steffen"
SaveWithValues "Heinz", "F", "Heinz"
SaveWithValues "Sigmar", "F", "Sigmar"
SaveWithValues "Manfred", "F", "Manfred"
SaveWithValues "Kurt", "F", "Kurt"
Application.ScreenUpdating = True
MsgBox "Daten wurden in Arbeitsmappen kopiert"
End Sub