我正在制作一个宏来复制选定工作表的选定行到另一个工作表。例如,我想把第3、5、6、7行复制到第3个工作表中。我考虑使用复选框选择行并使用单选按钮选择工作表。在我的代码中,我通过单选按钮设置一个变量,并使用该变量决定要将数据复制到哪个工作表中。
Public Val As String
Public Sub OptionButton1_Click()
If OptionButton1.Value = True Then Val = "Sheet2"
End Sub
Public Sub OptionButton2_Click()
If OptionButton2.Value = True Then Val = "Sheet3"
End Sub
Sub Addcheckboxes()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim MyLeft, MyTop, MyHeight, MyWidth As Double
Application.ScreenUpdating = False
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For cell = 2 To LRow
If Cells(cell, "A").Value <> "" Then
MyLeft = Cells(cell, "E").Left
MyTop = Cells(cell, "E").Top
MyHeight = Cells(cell, "E").Height
MyWidth = Cells(cell, "E").Width
ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
With Selection
.Caption = ""
.Value = xlOff
.Display3DShading = False
End With
End If
Next cell
Application.ScreenUpdating = True
End Sub
Sub CopyRows()
For Each chkbx In ActiveSheet.CheckBoxes
If chkbx.Value = 1 Then
For r = 1 To Rows.Count
If Cells(r, 1).Top = chkbx.Top Then
With Worksheets(Val)
LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & LRow & ":AF" & LRow) = _
Worksheets("Sheet1").Range("A" & r & ":AF" & r).Value
End With
Exit For
End If
Next r
End If
Next
End Sub
这里通过选项按钮1或2设置Val变量的值,并且将该值用于Sub CopyRows()函数中。但是,在CopyRows()函数的第4行出现了错误。 *错误信息显示为“下标越界”。* 你是否看出我的逻辑或其他问题?谢谢。(请原谅我在学习阶段可能存在的明显错误)。