AW: Tab beim Schließen als neue Datei speichern
03.03.2007 20:13:00
Erich
Hi Fritz,
so?
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strW As String, wb2 As Workbook, lngZ As Long, intC As Integer, objShape As Shape
With ThisWorkbook
strW = Left(.FullName, Len(.FullName) - 4) & _
"-" & .Sheets("Tabelle3").Cells(18, 16) & ".xls"
If Dir(strW) = "" Then
If .Sheets("Tabelle3").Cells(18, 20) = "1" Then
With .Sheets("Auswertung")
lngZ = .UsedRange.Row + .UsedRange.Rows.Count - 1
intC = .UsedRange.Column + .UsedRange.Columns.Count - 1
' anlegen, kopieren (auch Formate)
.Copy
Set wb2 = ActiveWorkbook
' Schaltflächen löschen
For Each objShape In ActiveSheet.Shapes
objShape.Delete
Next objShape
' nur Werte, keine Formeln
If lngZ * intC > 0 Then _
Range(Cells(1, 1), Cells(lngZ, intC)) = _
.Range(.Cells(1, 1), .Cells(lngZ, intC)).Value
End With
' speichern, schließen
wb2.SaveAs strW
wb2.Close
Set wb2 = Nothing
End If
Else
MsgBox strW & vbLf & "gibt es schon.", vbInformation
End If
End With
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort