Deleting a range when closing a workbook, xls vba
I need a range
Range("A2:G" & z)
for deleting with book closing - can anyone help me with the code?
thanks, Kay
here is what i tried:
Option Explicit
Sub Makro1()
'insert clipboard
Workbooks("Pfl_SchutzStat.xls").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'insert formulas to look up sheet ZTAXLIST
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,1)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,3)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,4)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,5)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,6)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,7)"
'autofill formulas
Dim z As Integer
z = Range("A2").End(xlDown).Row
Range("B2:G2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B2:G" & z)
Range("A1").Select
End Sub
here i added a sub that should delete the range i also tried to do this to close the workbook without saving and not asking for it i tried to insert msgbox - on close it seems like the macro is not called!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ev As Boolean
Dim datei As String
Dim z As Integer
z = Range("A2").End(xlDown).Row
datei = ThisWorkbook.Name
ev = Application.EnableEvents
Application.EnableEvents = False
If Workbooks.Count > 1 Then
ActiveWorkbook.Close SaveChanges:=False
Else
Workbooks(datei).Saved = True
Application.Quit
End If
Worksheets("Abfrage").Range("A2:G" & z).ClearContents
Application.EnableEvents = ev
End Sub
source to share
You can use the vba BeforeClose event function: find more information here: http://msdn.microsoft.com/en-us/library/aa220801%28v=office.11%29.aspx
Don't forget to include the worksheet where you want to delete your lines.
Here's an example:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'delete the lines of the "Sheet1"
Dim z As Integer
z = 2 'or whatever depending on your previous code
'replace "Sheet1" by the name of your sheet
Worksheets("Sheet1").Range("A2:G" & z).Delete Shift:=xlUp
End Sub
- = EDIT = -
First, you don't need to select every cell before setting the formula. For example, you could do:
Range("D2").FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,4)"
Instead:
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,4)"
Also, have you placed a personal Sub Workbook_BeforeClose (Cancel As Boolean) in the ThisWorkbook section in the VBA editor? It doesn't have to be in a module or in a sheet!
Hello,
Max
source to share