excel - 需要用户输入来指示代码

Question

我发现一些代码非常适合我想要的功能，即将整个工作表从一个工作簿复制到另一个工作簿，但我希望对其进行自定义并使其对我来说更容易一些(因此，我不必通过允许用户准确指定他们要复制的工作表来为所有 30 张工作表重新编码。

            Sub Start()
            Dim x As Workbook
            Dim y As Workbook

            '## Open both workbooks first:
            Set x = Workbooks.Open("data workbook")
            Set y = Workbooks.Open("destination workbook")

            'This is where I would like the user to input the destination sheet in x:
            x.Sheets("USER INPUT").Range("A1:z28").Copy

            'Then paste data from x to y:
            y.Sheets("STATIC SHEET").Range("A1").PasteSpecial

            'Close x:
            x.Close
            End Sub

我想要的是在运行宏时出现一个弹出框，允许用户输入要从中复制信息的工作表(位于“数据工作簿”中)的名称，并自动输入此输入访问要复制的数据时进入宏。

Answer 1

这已将 @TyMarc 的答案合并到您发布的代码中，但它还有一个错误处理程序，因此如果用户输入的名称不正确，它将给出错误消息并再次询问。

Sub Start()
    On Error GoTo ErrorHandler
    Dim x, y As Workbook
    Dim inp as String

    '## Open both workbooks first:
    Set x = Workbooks.Open("data workbook")
    Set y = Workbooks.Open("destination workbook")

Label1:

    inp = InputBox("Enter the name of the sheet to be copied")

    'This is where I would like the user to input the destination sheet in x:
    x.Sheets(inp).Range("A1:z28").Copy

    'Then paste data from x to y:
    y.Sheets("STATIC SHEET").Range("A1").PasteSpecial

    'Close x:
    x.Close
    Exit Sub 'This is a crutial part otherwise it will finish the program and continue right into the error handler which will send it back to Label1 and start an infinite loop of death...
ErrorHandler:
    MsgBox("The input entered was not the name of a worksheet")
    Resume Label1:
End Sub