如何实现用户选外部工作簿后指定工作表执行复制粘贴操作？

Absolutely! This exact workflow is totally achievable using Excel VBA. Let me walk you through a step-by-step solution that’s straightforward, user-friendly, and handles all your requirements:

Step 1: Add a Button to Your VSC Workbook #

First, add a clickable button to either the Compare or Plot sheet in your VSC workbook:

• Go to the Developer tab (enable it if it’s hidden via File > Options > Customize Ribbon)
• Click Insert > Choose the Button (Form Control)
• Draw the button on your sheet, then assign the macro we’ll write next (CopyFromSFWorkbook)

Step 2: Write the Core VBA Code #

Open the VBA editor (press Alt + F11), insert a new module, and paste this code. I’ve included comments to explain each part:

' Declare a module-level variable to share the SF workbook with the userform (if using Option 2)
Dim sfWorkbook As Workbook

Sub CopyFromSFWorkbook()
    Dim filePath As String
    Dim selectedSheet As Worksheet
    Dim sheetName As String
    Dim fileDialog As FileDialog
    
    ' Step 1: Open file picker to select the SF workbook
    Set fileDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fileDialog
        .Title = "Select the SF Workbook"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub ' Exit if user cancels
        filePath = .SelectedItems(1)
    End With
    
    ' Step 2: Open the SF workbook in the background (read-only to avoid lock issues)
    Application.ScreenUpdating = False
    Set sfWorkbook = Workbooks.Open(filePath, ReadOnly:=True)
    
    ' Step 3: Let user select a sheet (pick one option below)
    ' --- Option 1: Simple Input Box (good if users know sheet names) ---
    sheetName = InputBox("Enter the name of the sheet to copy from (e.g., Results1):", "Select Sheet")
    If sheetName = "" Then GoTo Cleanup ' Exit if user cancels
    
    ' --- Option 2: UserForm with ListBox (more user-friendly for many sheets) ---
    ' Uncomment this block and follow the UserForm setup instructions below
    'Dim sheetSelector As New UserForm1
    'sheetSelector.Show
    'sheetName = sheetSelector.SelectedSheetName
    'Unload sheetSelector
    'If sheetName = "" Then GoTo Cleanup
    
    ' Verify the selected sheet exists
    On Error Resume Next
    Set selectedSheet = sfWorkbook.Sheets(sheetName)
    On Error GoTo 0
    If selectedSheet Is Nothing Then
        MsgBox "Sheet '" & sheetName & "' not found in the selected workbook.", vbExclamation
        GoTo Cleanup
    End If
    
    ' Step 4: Copy and paste data (adjust ranges/paste type as needed)
    ' Example: Copy used range from SF sheet to A1 of VSC's Compare sheet
    selectedSheet.UsedRange.Copy
    ThisWorkbook.Sheets("Compare").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ' Use xlPasteAll instead if you want to copy formulas/formats too
    
    ' Confirm success
    MsgBox "Data copied successfully to the Compare sheet!", vbInformation

Cleanup:
    ' Cleanup operations
    Application.CutCopyMode = False
    If Not sfWorkbook Is Nothing Then sfWorkbook.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

Step 3: (Optional) Set Up the UserForm for Sheet Selection #

For a more intuitive experience (great if the SF workbook has many sheets), create a UserForm:

1. Insert a UserForm via Insert > UserForm in the VBA editor
2. Add:
   • A ListBox (name it lstSheets)
   • Two CommandButtons (name them cmdOK and cmdCancel)
3. Paste this code into the UserForm’s code window:

Public SelectedSheetName As String

Private Sub cmdCancel_Click()
    SelectedSheetName = ""
    Me.Hide
End Sub

Private Sub cmdOK_Click()
    If lstSheets.ListIndex <> -1 Then
        SelectedSheetName = lstSheets.Value
        Me.Hide
    Else
        MsgBox "Please select a sheet first.", vbExclamation
    End If
End Sub

Private Sub UserForm_Initialize()
    ' Populate the listbox with all sheet names from the SF workbook
    Dim ws As Worksheet
    For Each ws In sfWorkbook.Sheets
        lstSheets.AddItem ws.Name
    Next ws
End Sub

Then uncomment the Option 2 block in the main code to use this form.

Key Notes & Customizations #

• Adjust Copy/Paste Ranges: Replace UsedRange with a specific range (e.g., Range("A1:Z100")) if you don’t want to copy all used cells
• Paste Options: Use xlPasteAll to copy everything, xlPasteValues for just values, or xlPasteFormats for only formatting
• Error Handling: The code includes basic error checking, but you can expand it to handle cases like the SF workbook already being open
• Performance: Setting ScreenUpdating = False makes the process run smoothly without flicker

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