VBA-复制模板工作表并使用用户输入的文本重命名 [英] VBA - Copy a template worksheet and rename with user entered text

问题描述

这里的VBA新手-我已经梳理了互连网，但似乎无法使它正常工作，这让我很困惑.

VBA novice here - I've combed the interwebs and can't seem to get this to work, it's got me stumped.

我想在表单上有一个按钮，该按钮将允许用户在同一工作簿中在模板"的右侧生成工作表模板"的副本.我已经弄清楚了，可以生成一个将自身重命名为Template(2)的副本，或者生成一个以在提示中输入的文本命名的空白工作表，但是我不能两者都做.

I would like to have a button on a form that will allow a user to generate a copy of a worksheet "Template", in the same workbook - to the right of "Template". I've figured it out enough that I can generate a copy that renames itself as Template(2) OR generate a blank worksheet named with the text entered in the prompt, but I can't do both.

如下所示-当前返回对象必需"错误.预先感谢您的帮助，万分感谢！

As is below - it currently returns an "Object Required" error. Thanks in advance for your help, it's much appreciated!

Private Sub NewSheet()
 Dim NewSheet As Worksheet
 Dim newName As String
 Do
 newName = Application.InputBox("What do you want to name the new sheet?", Type:=2)
If newName = "False" Then Exit Sub: Rem cancel pressed 

Set NewSheet = ThisWorkbook.Worksheets("Template").Copy(After:=Worksheets("Template"))

On Error Resume Next
    NewSheet.Name = newName
    newName = Error
On Error GoTo 0

If newName <> vbNullString Then
    Application.DisplayAlerts = False
        NewSheet.Delete
    Application.DisplayAlerts = True
    MsgBox newName
End If
Loop Until newName = vbNullString

End Sub

推荐答案

或者您可以尝试以下方法:

Or you can try this:

Sub Test()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("Template")
    Dim newws As Worksheet, sh As Worksheet, newname
    Dim query As Long, xst As Boolean, info As String

retry:
    xst = False
    newname = Application.InputBox("Enter a new sheet name.", info, , , , , , 2)
    If newname = "False" Then Exit Sub
    For Each sh In wb.Sheets
        If sh.Name = newname Then
            xst = True: Exit For
        End If
    Next
    If Len(newname) = 0 Or xst = True Then
        info = "Sheet name is invalid. Please retry."
        GoTo retry
    End If
    ws.Copy after:=ws: Set newws = ActiveSheet: newws.Name = newname
End Sub

除非用户取消，否则它将连续询问有效的工作表名称.
为避免删除新添加的图纸，请首先检查名称是否有效.
另外，我不认为您可以一次性复制和分配，因为没有文档说明 Copy方法返回被复制的对象.因此，您首先复制并使用 Activesheet 将其分配给变量.

This will continuously ask for a valid sheet name unless the user cancels it.
To avoid deleting a newly added sheet, check first if the name is valid.
Also, I don't think you can copy and assign in one go since there's no documentation that the Copy Method returns the object that was copied. So you copy first and use Activesheet to assign it to a variable.

这篇关于VBA-复制模板工作表并使用用户输入的文本重命名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！