我的粘贴链接似乎无效,并且给了我一个在指定行上失败的范围类的选择方法。我似乎无法诊断此错误。
Sub CustomizedInputFixedoutputnotworking()
Dim rng As Range, _
inp As Range, _
ws As Worksheet
Set inp = Selection
On Error Resume Next
Set rng = Application.InputBox("Copy to", Type:=8)
On Error GoTo 0
If TypeName(rng) <> "Range" Then
MsgBox "Cancelled", vbInformation
Exit Sub
Else
Worksheets("Sheet 2").Range("B2:N5").Select ' Code does not work at this line
Worksheets("Sheet 2").Paste Links:=True
End If
Application.CutCopyMode = False
End Sub
试试这个代码:
Sub CustomizedInputFixedoutputnotworking()
Dim rng As Range, _
inp As Range, _
ws As Worksheet
Set inp = Selection
On Error Resume Next
Set rng = Application.InputBox("Copy to", Type:=8)
On Error GoTo 0
If TypeName(rng) <> "Range" Then
MsgBox "Cancelled", vbInformation
Exit Sub
Else
rng.Copy ' add this line to copy the range that user selected before (with InputBox)
Worksheets("Sheet 2").Activate ' add this line to activate the target worksheet, because select method (the next line) only work in the active sheet
Range("B2:N5").Select
ActiveSheet.Paste Link:=True
End If
Application.CutCopyMode = False
End Sub
注意:如果用户选择非连续范围(例如A1和B2),则复制方法将失败,避免这种情况的简单方法(不是完整方法)是使用:
Set rng = Union(rng, rng)
If rng.Areas.Count > 1 Then Exit Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句