从Excel复制数据到另一个Excel时出现Type Mismatch错误求助

Hey there! Let's break down why you're hitting that Type Mismatch error and get your code working smoothly again.

Root Cause of the Type Mismatch #

The immediate culprit is this line:

dataRange = Range("A1:T1")

You declared dataRange as a String, but Range("A1:T1") is a Range object—not a text string. Trying to assign an object to a string variable directly triggers the Type Mismatch error. We'll fix this by handling the data copy directly instead of storing it in a mismatched variable.

Other Issues in Your Code #

Let's address the other problems that are causing or will cause errors:

• Workbook.Open should be Workbooks.Open: It's a collection of workbooks, so the method name needs to be plural.
• Unnecessary Select statements: Using Select and Activate is unreliable (it can break if the user clicks elsewhere mid-macro). We'll reference sheets and ranges directly instead.
• Incorrect row count logic: Your current code tries to get a row count from Range("A3:T3").CurrentRegion, which won't give you the last empty row where you need to paste data.
• Incomplete With block: Your code cuts off, but we'll assume you were trying to paste the copied data and complete that logic properly.

Corrected Code #

Here's a revised version of your code that fixes all these issues:

Private Sub CommandButton1_Click()
    Dim sourceSheet As Worksheet
    Dim destWorkbook As Workbook
    Dim destSheet As Worksheet
    Dim lastDestRow As Long
    
    ' Reference the source sheet in your current workbook
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' Open the destination workbook and reference its Sheet1
    Set destWorkbook = Workbooks.Open("C:\Users\mahather\Desktop\Report\Test.xlsx")
    Set destSheet = destWorkbook.Worksheets("Sheet1")
    
    ' Find the last used row in the destination sheet, then move to the next empty row
    lastDestRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Copy the source range directly to the destination's empty row
    sourceSheet.Range("A1:T1").Copy destSheet.Range("A" & lastDestRow)
    
    ' Optional: Save and close the destination workbook if needed
    ' destWorkbook.Save
    ' destWorkbook.Close
    
    ' Clean up objects to free memory
    Set destSheet = Nothing
    Set destWorkbook = Nothing
    Set sourceSheet = Nothing
End Sub

Key Improvements #

• No more Type Mismatch: We skip the mismatched variable assignment and copy the range directly from source to destination.
• Reliable references: Using Set to target sheets/workbooks without Select makes the code more stable.
• Proper row targeting: We find the last used row in the destination sheet to ensure data is pasted in the next empty row (no overwriting existing content).
• Clean memory management: We set objects to Nothing to free up system resources.

If you had a specific use case for dataRange (like storing values for later manipulation), just let me know and we can adjust the code further!

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