...das sollte es sein...
31.10.2005 17:03:40
gordon
Hallo Gustav,
jaja, der arme Bankensektor ;-) kein Geld für IT...
Sub Gustav_Mark()
Dim rlc As Range, rToCopy As Range, _
rCopyStart As Range, rCopyEnd As Range, _
rSource As Range, c As Range, rToPaste As Range, _
wsHelp As Worksheet, _
wb As Workbook, _
strCostcentre As String
Set wsHelp = ThisWorkbook.Worksheets("Help")
'last cell in spalte k ermitteln
Set rlc = wsHelp.Range("K65536")
If Len(rlc) = 0 Then Set rlc = rlc.End(xlUp)
'rSource auf spalte j mit länge von spalte k legen
Set rSource = wsHelp.Range("J3:J" & rlc.Row)
For Each c In rSource
'startzelle ? -> prüfung nur auf inhalt, kein format/text/zahl etc.
If Len(c.Value) <> 0 Then
Set rCopyStart = wsHelp.Cells(c.Row, 1)
strCostcentre = c.Value
End If
'endzelle ?
If c.Offset(0, 1) = "Saldo" Then
Set rCopyEnd = c.Offset(0, 1)
End If
' rudimentäre plausibilitaet, z.B. ende ohne / vor start gefunden
If Not rCopyEnd Is Nothing Then
If rCopyStart Is Nothing Then
MsgBox "Fehler, kein Start für Ende in Zeile " & rCopyEnd.Row
Exit Sub
End If
' zu kopierender Bereich
Set rToCopy = wsHelp.Range(rCopyStart, rCopyEnd)
'neues workbook oeffnen
Set wb = Workbooks.Add
' bereich in worksheet1 einfügen
rToCopy.Copy (wb.Worksheets(1).Cells(1, 1))
' neues wb speichern und schliessen
wb.SaveAs ("c:\temp\tst_" & strCostcentre & ".xls"): wb.Close (False)
'init bereich
Set rCopyEnd = Nothing
Set rCopyStart = Nothing
End If
Next
End Sub
Gruß
gordon