活动工作表最后一行
m = range("a65536").end(xlup).row '一般情况
m = range("a" & rows.count).end(xlup).row '不做下限时
屏幕闪烁
Application.ScreenUpdating = False '关闭
Application.ScreenUpdating = True '打开
指定文件夹遍历所有工作簿的所有工作表
Dim mypath$, myfile$, ak As Workbook '定义变量
m = Sheet1.Range("a65536").End(xlUp).Row '删除历史记录'
If m > 2 Then
Sheet1.Rows("2:" & m).Clear
m = 1
End If
mypath = ThisWorkbook.Path & "\123\" '确定文件路径'
myfile = Dir(mypath & "*.xls") '确定指定路径'
Do While myfile <> "" '遍历文件夹'
If myfile <> ThisWorkbook.Name Then
Set ak = Workbooks.Open(mypath & myfile) '按照顺序打开文件'
Else
GoTo tiaozhuan '遍历结束跳转至末尾'
End If
For i = 1 To ActiveWorkbook.Worksheets.Count '遍历打开的工作簿中所有工作表'
With ak.Worksheets(i) '对单一表的操作'
nm = ak.Name
nm2 = .Name
n = .Range("a65536").End(xlUp).Row
pp = .Range("a2:s" & n)
n = n - 1
Sheet1.Range("a" & m + 1 & ":s" & m + n) = pp
Sheet1.Range("t" & m + 1 & ":t" & m + n) = nm & nm2
m = m + n
End With
Next i
ak.Close '关闭工作簿'
myfile = Dir '选择下一个工作簿'
Loop
tiaozhuan: '结束Do循环标签'
工作表隐藏
Sheet5.Visible = xlSheetVeryHidden '深度隐藏'
Sheet5.Visible = True '取消隐藏'
Sheet5.Visible = false '普通隐藏'
指定工作表打开(导入/导出)
temp = ThisWorkbook.Path & "\示例.xlsx" '确定文件路径'
Set a = GetObject(temp) '定义文件'
With a.Sheets("sheet1") '指定sheet进行操作'
m = .Range("n65536").End(xlUp).Row
b = .Range("a1:q" & m)
Sheet2.Range("a1:q" & m) = b
a.Close False '关闭工作簿'
End With
Set a = Nothing '初始化变量'
透视表刷新
Sheet1.PivotTables("数据透视表1").PivotCache.Refresh
审阅密码添加解除
Sheets("出库数据").Protect ("123456") '加密'
Sheets("出库数据").Unprotect ("123456") '解密'
添加批注
Sheet1.Cells(a, 15).AddComment Text:=Sheet6.Cells(b, 7)
定点执行
Application.OnTime TimeValue("04:00:00"), "MySub"