1104VBA学习--成功版
'open the .xls file Option Explicit
Const MSEXCEL_FILENAME = "Excel文件路径"
Sub VBMain()
'加快运行速度,暂停表格刷新和禁止弹出框。
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'遍历每个sheet
Dim xlApp, WorkBooks, Sheet
Set xlApp = WSH.CreateObject("Excel.Application")
<!-- more -->
xlApp.Visible = False
Set WorkBooks = xlApp.Workbooks.Open(MSEXCEL_FILENAME)
Set Sheet = WorkBooks.Worksheets(1)
'打开第一个工作表
' 也可以打开上次保存时打开的活动工作表
' Set Sheet = WorkBooks.ActiveSheet 打开活动的工作表
WorkBooks.Close
end sub
'select data
Range("G:G").Select
Selection.AutoFilter
ActiveSheet.Range("$G$1:$G$200").AutoFilter Field:=1, Criteria1:="fail"
'copy and paste data to other sheet
ActiveSheet.Range("$G$1:$G$200").AutoFilter Field:=1, Criteria1:="电声工程师"
Rows("2:2").Select
Selection.Copy
Sheets("Sheet1").Select
Rows("1:1").EntireRow.AutoFit
Rows("1:1").Select
ActiveSheet.Paste
'获取当前sheet页的名称
fname = ActiveSheet.Name
'get the end of excel
Sheet.UsedRange.Rows.Count或者Sheet.UsedRange.Columns.Count
Sheets(i & "yue").UsedRange.Rows.Count
'choose entire columns
Selection.EntireColumn.Select
Selection.EntireRow.Selectactivecell.address'这是当前单元格地址 selection.offset(1, 0).select'这是向下跳1格 selection.offset(-1, 0).select'这是向上跳1格 selection.offset(0, -1).select'这是向左跳1格 selection.offset(0, 1).select'这是向右跳1格
1.搜索表中的内容并且获得位置
1.缺少遍历所有sheet
2.将参数设置成变量
Sub 全套服务连续版本()
'
' 全套服务连续版本 宏
'
'创建新sheet 宏
'
'
dim resultSheetName as String
dim searchResultLocation as string
dim sheetCopyRange as string
dim sheetPasteRange as string
dim currentSheetName as string
dim colmunsCount As Integer
dim rowsCount As Integer
'新建一个sheet存放失败项
Sheets.Add After:=ActiveSheet
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "failResult"
'begin to lookup
'查找Result行并获取result行的位置
Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'search reasult and get the location,filter the Fail data
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'选择有效数据
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表格
Selection.Copy
Sheets("failResult").Select
Range("A65536").End(3).Row + 1
ActiveSheet.Paste
Range("A14").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "AVCVBS"
Selection.AutoFill Destination:=Range("A14:A24"), Type:=xlFillDefault
Range("A14:A24").Select
Range("F20").Select
End Sub
'get cell's address
F_Max_Range = ActiveCell.Address() '获得单元格的地址,形式为$A$1
F_Max_Range = ActiveCell.Address(0, 0) '获得单元格的地址,形式为A1
F_Max_Range = ActiveCell.Address(0, 1) '获得单元格的地址,形式为$A1
F_Max_Range = ActiveCell.Address(1, 0) '获得单元格的地址,形式为A$1
F_Max_Range = ActiveCell.Address(1, 1) '获得单元格的地址,形式为$A$1Dim CONSTCOLUMNS As String
' Dim searchResultLocation As String
Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, MatchByte:=False, SearchFormat:=False).Activate searchResultLocation = ActiveCell.Address(0, 0) CONSTCOLUMNS = Left(searchResultLocation, 1) + "1000" Selection.EntireColumn.Select ActiveSheet.Colmuns(searchResultLocation, CONSTCOLUMNS).Select
获取最后一行
'为最后一个单元格所在行 , 即最后一行,该函数表示Sheet表的使用区域中的最后一行
nLastUseRow = Cells.SpecialCells(xlCellTypeLastCell).Row
' 得到A列单元格的最后一行
strCell = "A" + CStr(nLastUseRow + 1)ActiveCell.Address(0, 0) & Left(searchResultLocation, 1) + "800"
excel表格不是从第一行开始的,也不是隐藏
冻结窗口了
Sub SetCursorToCell(iRow As Integer, iCol As Integer)
Dim pt As POINTAPI
Dim iTRow As Integer
Dim iTCol As Integer
iTRow = ActiveWindow.VisibleRange.Rows.Count
iTCol = ActiveWindow.VisibleRange.Columns.Count
If iRow < ActiveWindow.VisibleRange.Row Or iRow > ActiveWindow.VisibleRange.Row + iTRow - 1 Or iCol < ActiveWindow.VisibleRange.Column Or iCol > ActiveWindow.VisibleRange.Column + iTCol - 1 Then
Cells(iRow, iCol).Select
End If
pt.x = ActiveWindow.PointsToScreenPixelsX(0) + _
Point2PixelX(Cells(iRow, iCol).Left)
pt.y = ActiveWindow.PointsToScreenPixelsY(0) + _
Point2PixelX(Cells(iRow, iCol).Top)
SetCursorPos pt.x, pt.y
End SubDirection XlDirection 类型,必需。所要移动的方向。
XlDirection 可为 XlDirection 常量之一。
xlDown
xlToRight
xlToLeft
xlUp
二、End(xlUp).Row示例
本示例选定包含单元格 B4 的区域中 B 列顶端的单元格。
Range("B4").End(xlUp).Select
本示例选定包含单元格 B4 的区域中第 4 行尾端的单元格。
Range("B4").End(xlToRight).Select
版本1.0
'20201104 1521V1.0
'能对单个表进行操作,缺少遍历
'多重区域选定会出bug
'优化:可以,没能插入表名称
' test2 宏
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim currentSheetName As String
Dim colmunsCount As Integer
Dim rowsCount As Integer
'新建一个sheet存放失败项
Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'search reasult and get the location
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
Selection.Copy
Sheets("failResult").Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
ActiveSheet.Paste版本2.0
'添加注释
Sub test2()
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' test2 宏
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim currentSheetName As String
Dim colmunsCount As Integer
Dim rowsCount As Integer
'新建一个sheet存放失败项
'获取表名字
fname = ActiveSheet.Name
'搜索并确定result位置
Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets("failResult").Select
'插入新得行再复制粘贴
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
ActiveSheet.Paste
'选择有效数据
End Sub版本3.0
能够遍历
不能写名字
能够复制
不能重复选
Sub 宏1()
'添加注释
'
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim currentSheetName As Excel.Worksheet
Dim colmunsCount As Integer
Dim rowsCount As Integer
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.Count
For Each currentSheetName In ThisWorkbook.Worksheets
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
activesheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets("failResult").Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
colmunsCount = ActiveSheet.UsedRange.Columns.Count
rowsCount = ActiveSheet.UsedRange.Rows.Count
ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
Next
End Sub版本4.0
需要判断是否有fail项目,如果没有就跳出
Sub 宏1()
'添加注释
'
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim currentSheetName As Excel.Worksheet
Dim colmunsCount As Integer
Dim rowsCount As Integer
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.Count
For Each currentSheetName In ThisWorkbook.Worksheets
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets("failResult").Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
Next
End Sub版本5.0
Sub failResult()
'添加注释
'
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim currentSheetName As Excel.Worksheet
Dim colmunsCount As Integer
Dim rowsCount As Integer
'判断是否存在failResult文件夹
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = "failResult" Then
Exit For
Else
Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "failResult"
End If
Next currentSheetName
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets("failResult").Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
Next
'
End Sub版本V6.0
Sub 脚本命令()
'添加注释
'
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'判断是否存在failResult文件夹
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "failResult"
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets("failResult").Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
Next
'
End Sub版本v7.0
Sub 脚本命令()
'添加注释
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'判断是否存在failResult文件夹
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "failResult"
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = "failResult" Then GoTo nexttime
End If
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets("failResult").Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
'
End Sub版本V9.0
Sub 脚本命令()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
'
End Sub版本V10.0
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
MsgBox "failresult"
End If
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
MsgBox "执行完一次"
nexttime:
Next
'
End Subv11.0
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
'
End Sub版本V12
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
'
End Sub若要对一个可能包含多重选定区域的区域使用此属性,请用 Areas.Count 方法测试一下此区域内是否包含多个子区域。如果确实包含多个子区域,可对此区域内的每一子区域进行循环。
V13.0
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
ActiveSheet.UsedRange.AutoFilter
MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
'
End SubV15(202011051606 可用)
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
'MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
ActiveSheet.UsedRange.AutoFilter
' MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 3).Select
'ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
Sheets(currentSheetName.Name).Range("a1").CurrentRegion.Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
MsgBox "所有失败项都已放置在failResult中"
End SubV17.0(不可用)
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
'判断是否有空表,有则删除
If IsBlankSht(currentSheetName) Then
currentSheetName.Delete
End If
Next
If judge.exists(s) Then
GoTo begin
Else
'MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
'不对failResult进行操作
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
ActiveSheet.UsedRange.AutoFilter
' MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 3).Select
'ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
Sheets(currentSheetName.Name).Range("a1").CurrentRegion.Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
End Sub
Function IsBlankSht(Sh As Variant) As Boolean
If TypeName(Sh) = "String" Then Set Sh = Worksheets(Sh)
If Application.CountA(Sh.UsedRange.Cells) = 0 Then
IsBlankSht = True
End If
End Function
V17.1
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
Dim sheetCopyRange As String
Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
Dim colmunsCount As Integer
Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
'判断是否有空表,有则删除
If Cells.Find("*") Is Nothing Then
Sheets(currentResultName.Name).Delete
End If
Next
If judge.exists(s) Then
GoTo begin
Else
'MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
'不对failResult进行操作
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
ActiveSheet.UsedRange.AutoFilter
' MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 3).Select
'ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
Sheets(currentSheetName.Name).Range("a1").CurrentRegion.Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
End SubV18 11051635
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
'Dim sheetCopyRange As String
'Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
'Dim colmunsCount As Integer
'Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
'MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
'判断表是否为空
If ShIsBlank(currentSheetName) = True Then
currentSheetName.Delete
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
ActiveSheet.UsedRange.AutoFilter
' MsgBox currentSheetName.Name
'获取表名字
' fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 3).Select
'ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
Sheets(currentSheetName.Name).Range("a1").CurrentRegion.Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
MsgBox "所有失败项都已放置在failResult中"
End Sub
Function ShIsBlank(sht As Variant) As Boolean
If TypeName(sht) = "string" Then
Set sht = Worksheets(sht) 'sht是工作表对象,完全可以作为Worksheets集合的参数加以利用.
End If
If Application.WorksheetFunction.CountA(sht.UsedRange.Cells) = 0 Then
ShIsBlank = True
End If
End FunctionV19.00 11051659(keyong)
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
'Dim sheetCopyRange As String
'Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
'Dim colmunsCount As Integer
'Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
'MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
'判断表是否为空
If ShIsBlank(currentSheetName) = True Then
currentSheetName.Delete
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
ActiveSheet.UsedRange.AutoFilter
' MsgBox currentSheetName.Name
'获取表名字
fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 3).Select
'ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
Sheets(currentSheetName.Name).Range("a1").CurrentRegion.Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 3).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Value = "上一个工作表的名字:" + Sheets(currentSheetName.Name).Name
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
MsgBox "所有失败项都已放置在failResult中"
End Sub
Function ShIsBlank(sht As Variant) As Boolean
If TypeName(sht) = "string" Then
Set sht = Worksheets(sht) 'sht是工作表对象,完全可以作为Worksheets集合的参数加以利用.
End If
If Application.WorksheetFunction.CountA(sht.UsedRange.Cells) = 0 Then
ShIsBlank = True
End If
End Function