【急急急】关于夸表执行宏命令
本帖最后由 xiaolinyouni 于 2011-09-17 10:47:42 编辑 我的宏命令在A表里面,然后在A表中把宏命令加到菜单栏的按钮上了,这样打开其他的excel都有这个按钮.(为了防止一次次导入模块的麻烦操作)
我要处理B表的东西,只打开B表,点击按钮,宏命令默认打开A表,
结果直接处理A表了
怎样做才能在B表中使用宏命令能处理B表的内容呢!
上代码
Sub WebDiv()
'将Researchercomments中的nonthingnew分离出来
Dim i, finalColume As Integer
Dim aRow, aColume As Integer
Dim bRow As Integer
ThisWorkbook.Worksheets(1).Activate
aRow = Range("A1").CurrentRegion.Cells.Rows.Count '有效单元格的行数
aColume = Range("A1").CurrentRegion.Cells.Count / aRow '有效单元格的列数
'查找表头的"ResearcherComments"项
finalColume = 0
For i = 1 To aColume
If Trim(ActiveSheet.Cells(1, i)) = "ResearcherComments" Then
finalColume = i
ActiveSheet.Name = "Test Sheet1"
ActiveSheet.Rows("1:1").Select
ActiveSheet.Rows("1:1").Copy
If ThisWorkbook.Worksheets.Count < 2 Then
ThisWorkbook.Worksheets.Add after:=ThisWorkbook.Worksheets(1)
End If
ThisWorkbook.Worksheets(2).Activate
ActiveSheet.Cells(1, 1).Select
ActiveSheet.Paste
ActiveSheet.Name = "NoProjects"
ThisWorkbook.Worksheets(1).Activate
Exit For
End If
Next i
If finalColume = 0 Then Exit Sub
'如果查找不到表头的"ResearcherComments"则终止程序
bRow = 1
For i = 2 To aRow
If i > aRow Then Exit For
If Trim(ActiveSheet.Cells(i, finalColume)) = "Nothing New" Then
ActiveSheet.Rows(Trim(i) & ":" & Trim(i)).Select
ActiveSheet.Rows(Trim(i) & ":" & Trim(i)).Cut
ThisWorkbook.Worksheets(2).Activate
ActiveSheet.Cells(bRow + 1, 1).Select
ActiveSheet.Paste
bRow = bRow + 1
ThisWorkbook.Worksheets(1).Activate
ActiveSheet.Rows(Trim(i) & ":" & Trim(i)).Select '选中原先的空行(剪切并粘贴后变成空行)
ActiveSheet.Rows(Trim(i) & ":" & Trim(i)).Delete Shift:=xlUp '删除,并将表格往上提
aRow = aRow - 1
End If
Next
End Sub
