错误429"Activex组件无法创建对象".将单元格复制到新工作表时的列 [英] Error 429 "Activex component can't create object" when copying Cells to new worksheet Column

问题描述

此代码复制列P&每个工作表中的Q，并将它们过帐到合并的新工作表中.它还会删除所有空白单元格.

This code copies columns P & Q from each worksheet and posts them to a new worksheet consolidated. It also deletes all blank cells.

代码在一个很小的文件上工作，但在新工作簿上无法产生相同的结果.

The code works on a very small file but isn't producing the same results on the new workbook.

从以前的小型工作簿"到新工作簿的所有列都是相同的.唯一更改的是工作表数量为650.

ALL COLUMNS from previous "small workbook" to new workbook are the same. The only thing that changed is the number of worksheets which is 650.

我收到运行时错误429"Activex组件无法创建对象".

I get a runtime error 429 "Activex component can't create object".

Sub merge()

    Dim Sh As Worksheet, ShM As Worksheet, i&, z&
    Application.ScreenUpdating = 0
    Set Sh = Worksheets.Add(, Sheets(Sheets.Count))
    Sh.Name = "consolidated"
    For Each ShM In ThisWorkbook.Worksheets
        If ShM.Name <> Sh.Name Then
            i = ShM.Cells(Rows.Count, 17).End(xlUp).Row
            z = Sh.Cells(Rows.Count, 2).End(xlUp).Row + 1
            While (z > 1) And (Sh.Cells(z, 2).Value = "")
                z = z - 1
            Wend
            ShM.Activate: ShM.Range(Cells(1, 16), Cells(i, 17)).Copy
            Sh.Activate: Sh.Cells(z, 1).PasteSpecial xlPasteValues
        End If
    Next ShM
    Application.ScreenUpdating = 1
End Sub

推荐答案

此错误不是由您发布的代码引起的，请查看以下内容: 自动化Office应用程序时会收到运行时错误429

This error is not caused by the code you've posted, please see this: You receive run-time error 429 when you automate Office applications

此问题也在这里讨论:

运行时错误"429":ActiveX组件无法创建对象VBA

检查工具〜>引用中缺少的组件

这篇关于错误429"Activex组件无法创建对象".将单元格复制到新工作表时的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！