VBA循环遇Type 13错误求助，多工作表数据复制方案咨询

Hey there! Let's break down your problem step by step—first fixing that Type 13 mismatch error, then talking about the best way to handle your data aggregation for the pivot table.

First, that Type 13 error almost always boils down to a data type mismatch or ambiguous object reference in your code. Let's look at the most likely culprits from your snippet:

• Ditching Active... references: When you open COOupdate, ActiveSheet might not be the worksheet you actually want to copy data from. Instead of relying on active objects, explicitly reference the worksheets in the source workbook. For example:

  ' Instead of Set ws = Active...
  Set ws = COOupdate.Worksheets("YourSheetName") ' Replace with your actual sheet name
  ' Or loop through all 19 sheets like this:
  ' For Each ws In COOupdate.Worksheets
  

• Checking nrow calculation: If you're calculating nrow with something like Range("A" & Rows.Count).End(xlUp).Row, make sure you're referencing the correct worksheet (not the active one!). Use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row to tie it directly to your source sheet ws.
• Verifying paste range compatibility: If you're using PasteSpecial, ensure the source data type matches what the destination range expects. For example, don't try to paste formulas into a cell formatted as text, or vice versa.

Copy-pasting is functional, but it's not the most efficient or reliable method—especially with 19 sheets. Here's a better way:

• Direct value assignment (no copy/paste): Skip the clipboard entirely by assigning values directly between ranges. This is faster, avoids clipboard conflicts, and reduces error risk. Example:

  Dim destWB As Workbook
  Set destWB = ThisWorkbook ' Assume this is your pivot table workbook
  
  For Each ws In COOupdate.Worksheets
      ' Get last row of data in source sheet
      Dim sourceLastRow As Long
      sourceLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      
      ' Get first empty row in destination sheet
      Dim destLastRow As Long
      destLastRow = destWB.Worksheets("PivotData").Cells(destWB.Worksheets("PivotData").Rows.Count, "A").End(xlUp).Row + 1
      
      ' Copy data directly
      destWB.Worksheets("PivotData").Range("A" & destLastRow).Resize(sourceLastRow - 1, ws.UsedRange.Columns.Count).Value = _
          ws.Range("A2:" & ws.Cells(sourceLastRow, ws.UsedRange.Columns.Count).Address).Value
  Next ws
  

• Use arrays for large datasets: If your sheets have tons of data, load the source data into a VBA array first, then write it to the destination. This is way faster than range-to-range assignment:

  Dim dataArray As Variant
  ' Load source data into array
  dataArray = ws.Range("A2:" & ws.Cells(sourceLastRow, ws.UsedRange.Columns.Count).Address).Value
  ' Write array to destination
  destWB.Worksheets("PivotData").Range("A" & destLastRow).Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray
  

• Optimize performance: Add these lines at the start/end of your sub to speed things up and prevent screen flickering:

  ' At the top
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  ' At the bottom (before End Sub)
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  

• Explicit workbook references: Never rely on ActiveWorkbook—always use variables like COOupdate and destWB to avoid mixing up workbooks by accident.

Wrap-up: Fix that Type 13 error by cleaning up ambiguous references first, then switch to direct value/array assignment for a faster, more robust solution for your pivot table data.

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