|
我想采用模块代码写入到2个工作表vb窗口,下面代码如何修改?
Sub 向指定工作表写入代码()
Dim sh As Worksheet
Dim Code As String
Code = ""
Code = Code & "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
Code = Code & "If Intersect(Target(1), Columns(""A:D"")) Is Nothing Then Exit Sub" & vbCrLf'在这里写入代码
Code = Code &"Sheets(""概算表"").Cells(Target(1).Row, Target(1).Column) = Target" & vbCrLf ',使两个工作表内容同步
Code = Code & "End Sub" & vbCrLf
For Each sh In ThisWorkbook.Worksheets
If Not sh.Name <> "概算调整" Then
With ActiveWorkbook.VBProject.VBComponents(sh.CodeName).CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, Code
End With
'sh.Visible = False '要使这N张表都处于隐藏状态可在此加入此句
End If
Next
End Sub
- Sub 向指定工作表写入代码()
- Dim sh As Worksheet
- Dim Code1$, Code2$
- 第一个工作表名称 = "Sheet1"
- 第二个工作表名称 = "Sheet2"
- Code1 = ""
- Code1 = Code1 & "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
- Code1 = Code1 & "If Intersect(Target(1), Columns(""A:D"")) Is Nothing Then Exit Sub" & vbCrLf '在这里写入代码
- Code1 = Code1 & "Sheets(""概算表"").Cells(Target(1).Row, Target(1).Column) = Target" & vbCrLf ',使两个工作表内容同步
- Code1 = Code1 & "End Sub" & vbCrLf
- Code2 = ""
- Code2 = Code2 & "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
- '..........
- '..........
- Code2 = Code2 & "End Sub" & vbCrLf
- For Each sh In ThisWorkbook.Worksheets
- If sh.Name = 第一个工作表名称 Then
- With ActiveWorkbook.VBProject.VBComponents(sh.CodeName).CodeModule
- NextLine = .CountOfLines + 1
- .InsertLines NextLine, Code1
- End With
- End If
- If sh.Name = 第二个工作表名称 Then
- With ActiveWorkbook.VBProject.VBComponents(sh.CodeName).CodeModule
- NextLine = .CountOfLines + 1
- .InsertLines NextLine, Code2
- End With
- End If
- Next
- End Sub
复制代码
|
评分
-
查看全部评分
|