用户输入框，用于提供要用作源代码的工作表名称

Question

浏览了一些与用户输入/工作表名称相关的帖子，但没有一篇符合我的要求。我有一个简单的代码，可以根据预设条件选择列。当前代码指向一个固定的图纸名称。我希望代码打开一个输入框，用户选择源工作表上的任何单元格，然后代码将指定ws=工作表("desiredSheetname"(

Dim ws As Worksheet, lastRow As Long
Dim myNamedRange As Range, Rng As Range, c As Range, destrange As Range
Dim myRangeName As String
Dim SearchRow As Long
Dim StartatRow As Long
Dim desiredSheetName As String
desiredSheetName = Application.InputBox("Select any cell inside the source sheet: ", _
"Prompt for selecting target sheet name", Type:=8).Worksheet.Name

Set ws = Sheet.desiredSheetName  ------ error object required
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
.....

知道如何写ws行以链接正确的工作表名称吗？

Answer 1

通过应用程序输入框范围引用工作表

Option Explicit
Sub WorksheetFromInput()
' Allow hitting enter if the appropriate cell was already selected.
Dim aibDefault As String
If TypeOf Selection Is Range Then aibDefault = Selection.Address

Dim rg As Range

On Error Resume Next
Set rg = Application.InputBox( _
Prompt:="Select any cell inside the source sheet: ", _
Title:="Prompt for selecting target sheet name", _
Default:=aibDefault, _
Type:=8)
On Error GoTo 0

If rg Is Nothing Then
MsgBox "You canceled.", vbExclamation
Exit Sub
End If

Dim ws As Worksheet: Set ws = rg.Worksheet
Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

MsgBox "Name: " & ws.Name & vbLf & "LastRow: " & LastRow, vbInformation

End Sub

您可以通过以下方式清除Application.InputBox：

Sub WorksheetFromSelection()
Dim ws As Worksheet
On Error Resume Next
Set ws = Selection.Parent
On Error GoTo 0
If ws Is Nothing Then
MsgBox "No worksheet selected."
Exit Sub
End If
Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

MsgBox "Name: " & ws.Name & vbLf & "LastRow: " & LastRow, vbInformation
End Sub