最近做了一个VBA的小case,用于方便excel数据的处理,主要的功能代码记录如下。
1. 根据表单名称从workbook中查找出特定表单:
For Each sitem In ThisWorkbook.Worksheets
If sitem.Name = sname Then
' sitem is the object that we wants
Exit For
End If
Next
2. 复制表单m的特定内容到表单n:
Sheets(m).Range("A10:C11").Copy Sheets(n).Cells(1, 1)
3. 删除表单特定区域或者是特定区域的数据验证逻辑规则:
Sheets(m).Range("A10:C11").Delete
Sheets(m).Range("A10:C11").Validation.Delete
4. 添加新的worksheet并更改其名称:
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = sname 'ActiveSheet is the new one
5.具体代码
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
Dim i As Integer
Dim j As Integer
Dim sname As String
Dim sperson As String
Dim rgtemp As String
sname = ActiveSheet.Cells(1, 2).Text
sperson = ActiveSheet.Cells(1, 4).Text
If Sheet3.Cells(r, c).Text <> "" Or IsEmpty(sname) Then
MsgBox ("A new sheet (Rig.: " + sname + "; Resp. person: " + sperson + ";) is about to be created.")
Worksheets.Add
ActiveSheet.name = sname
Sheet2.Cells.Copy ActiveSheet.Cells(1, 1)
rgtemp = "B3:E" + Trim(Str(r))
Sheet3.Range(rgtemp).Copy ActiveSheet.Cells(18, 5)
ActiveSheet.Cells(5, 3).Value = sname