7,763
社区成员
发帖
与我相关
我的任务
分享
Option Explicit
Sub Get19From20(strSheetName As String, lFirstRow As Long, lLastRow As Long)
Dim shtSheet As Worksheet, shtCurSheet As Worksheet, lSheetIndex As Long, lRow As Long
On Error Resume Next
Set shtSheet = Worksheets(strSheetName)
If Err.Number <> 0 Then
MsgBox "在当前工作簿中不存在名为“" & strSheetName & "”的工作表!请检查后重试。", _
vbExclamation, "工作表不存在"
Exit Sub
End If
lSheetIndex = 0
For lRow = lFirstRow To lLastRow
lSheetIndex = lSheetIndex + 1
On Error Resume Next
Set shtCurSheet = Worksheets(CStr(lSheetIndex))
If Err.Number <> 0 Then
On Error GoTo ErrorHandle
Set shtCurSheet = Worksheets.Add(After:=Worksheets(lSheetIndex))
shtCurSheet.Name = CStr(lSheetIndex)
End If
On Error GoTo ErrorHandle
shtCurSheet.UsedRange.ClearContents
shtSheet.UsedRange.Copy shtCurSheet.Range("A1")
shtCurSheet.Rows(lRow).Delete Shift:=xlUp
Next lRow
MsgBox "物种点数据提取完毕,共从表“" & strSheetName & _
"”的第" & lFirstRow & "行到第" & lLastRow & "行中提取" & lSheetIndex & "次数据。" & _
vbCrLf & "留下第 n 个物种点数据行的提取结果存放在以 n 为名字的工作表中。", , "提取完毕"
Set shtCurSheet = Nothing
Set shtSheet = Nothing
Exit Sub
ErrorHandle:
MsgBox "正在留下第" & lSheetIndex & "个物种点数据行以提取数据的时候出现错误,程序停止运行。" & _
vbCrLf & "当前用以保存结果数据的工作表是“" & lSheetIndex & "”。以下为出错信息:" & _
vbCrLf & vbCrLf & "Error # " & Err.Number & ", " & Err.Description & _
vbCrLf & "Source: " & Err.Source, vbCritical, "程序异常中止"
Set shtCurSheet = Nothing
Set shtSheet = Nothing
End Sub
Sub DoIt()
Const SourceSheet As String = "源"
Get19From20 SourceSheet, 2, 21
End Sub