求助：如何用VBA代码新建命名工作表并复制指定单元格至其中

Got it, let's tackle this step by step—this is actually a straightforward combo of two tasks you already know, just stitched together properly. Here's a complete, tested solution that does exactly what you need:

Step 1: The Full Working Code #

Sub CreateNamedSheetAndCopyContent()
    Dim newSheet As Worksheet
    Dim sheetName As String
    Dim sourceRange As Range
    
    ' --- Customize these values to match your needs ---
    sheetName = "MyCustomSheet" ' Or pull from a cell like: sheetName = ThisWorkbook.Sheets("SourceSheet").Range("A1").Value
    Set sourceRange = ThisWorkbook.Sheets("SourceSheet").Range("B2:D10") ' Your target cells to copy
    ' ---------------------------------------------------
    
    ' Create the new worksheet and assign a name
    Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    
    ' Handle duplicate sheet name errors gracefully
    On Error Resume Next
    newSheet.Name = sheetName
    If Err.Number <> 0 Then
        MsgBox "Sheet name '" & sheetName & "' already exists! Using default name instead.", vbExclamation
        Err.Clear
    End If
    On Error GoTo 0 ' Reset error handling
    
    ' Copy the source range directly to the new sheet (starts at cell A1)
    sourceRange.Copy Destination:=newSheet.Range("A1")
    
    ' Optional: Clear the clipboard to remove the "marching ants" border
    Application.CutCopyMode = False
End Sub

Key Breakdown of the Code #

• Customization Section: The top block lets you tailor the code to your workbook:
  • Set sheetName to a hardcoded name, or pull it from a specific cell (like Sheets("SourceSheet").Range("A1").Value if you want the sheet name to come from your data)
  • Define sourceRange as the exact cells you want to copy over
• Sheet Creation: Sheets.Add creates a new sheet at the end of your workbook. The error handling ensures you won't hit a runtime error if the sheet name already exists.
• Content Copy: The Copy method with Destination pastes your range directly to the new sheet (starting at A1 by default—change this to any cell like newSheet.Range("C3") if you need a different starting point).
• Cleanup: The optional Application.CutCopyMode = False clears the clipboard so you don't see the dashed border around your copied range anymore.

How to Use This #

1. Open your Excel workbook
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer > Insert > Module
4. Paste the code above into the module
5. Adjust the sheetName and sourceRange values to match your specific setup
6. Press F5 to run the macro, or assign it to a button in your workbook for one-click access

内容的提问来源于stack exchange，提问作者Elin