我想要范围
Range("A2:G" & z)
关闭工作簿时删除 - 有人可以帮我处理代码吗?
谢谢,
凯
这就是我尝试过的:
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
在这里我添加了应该删除范围的子项
我还尝试关闭工作簿而不保存也不询问
我尝试插入一个消息框 - 关闭时似乎未调用宏!
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
您可以使用事件vba函数收盘前:在这里找到更多信息:http://msdn.microsoft.com/en-us/library/aa220801%28v=office.11%29.aspx http://msdn.microsoft.com/en-us/library/aa220801%28v=office.11%29.aspx
不要忘记指向要删除行的工作表。
这是一个例子:
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=-
首先,您不需要在设置公式之前选择每个单元格。例如你可以这样做:
Range("D2").FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,4)"
代替 :
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,ZTAXLIST!C2:C9,4)"
此外,您是否将 Private Sub Workbook_BeforeClose(Cancel As Boolean) 放在 VBA 编辑器的 ThisWorkbook 部分中?它不应该位于模块或工作表模块中!
Regards,
Max
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)