VBA 运行时错误 1004“应用程序定义或对象定义错误"选择范围时 [英] VBA Runtime Error 1004 "Application-defined or Object-defined error" when Selecting Range

问题描述

我在选择范围时遇到错误 1004应用程序定义或对象定义错误"的问题.

I am having an issue with a Error 1004 "Application-defined or Object-defined error" when selecting a range.

我仍然可以选择行(即 Rows("21:21").select)并在同一工作簿的其他工作表中选择范围.我不相信错误出在代码中.也许是我不知道的一些设置?

I am still able to select rows (ie Rows("21:21").select) and to select ranges in other sheets of the same workbook. I do not believe the error is in the code. Maybe its some setting I am unaware of?

我之前多次使用完全相同的代码，但由于某种原因我无法让它在这个子函数中运行(我已经评论了错误发生的地方)...

I have used the exact same code many times before but for some reason I cannot make it function in this sub (I have commented where the error occurs)...

Sub CopySheet1_to_PasteSheet2()

    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer

    'Finds last row of content
    Windows("Excel.xlsm").Activate
    Sheets("Sheet1").Activate
    Range("C21").Select
         '>>>Error 1004 "Application-defined or Object-defined error" Occurs
    Selection.End(xlDown).Select
    CLastFundRow = ActiveCell.Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1

    'Copy Data
    Range("A21:C" & CLastFundRow).Select
    Selection.Copy
    'Paste Data Values
    Sheets("PalTrakExport PortfolioAIdName").Select
    Range("A21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'Bring back to top of sheet for consistancy
    Range("A21").Select
    Range("A1").Select
End Sub

由于行数会经常变化，所以我需要在复制中花心思.同样，之前使用过以下代码没有错误......但不是在这种情况下.

I need to get all fancy in my copying as the amount of rows will change frequently. Again, the below code has been used before without error... but not in this instance.

Dim CLastFundRow As Integer
Dim CFirstBlankRow As Integer

'Finds last row of content
Windows("Excel.xlsm").Activate
Sheets("Sheet1").Activate
Range("C21").Select
     '>>>Error 1004 "Application-defined or Object-defined error" Occurs
Selection.End(xlDown).Select
CLastFundRow = ActiveCell.Row
'Finds first row without content
CFirstBlankRow = CLastFundRow + 1

推荐答案

也许你的代码在 Sheet1 后面，所以当你把焦点移到 Sheet2 时，找不到对象?如果是这种情况，只需指定您的目标工作表可能会有所帮助:

Perhaps your code is behind Sheet1, so when you change the focus to Sheet2 the objects cannot be found? If that's the case, simply specifying your target worksheet might help:

Sheets("Sheet1").Range("C21").Select

我不太熟悉 Select 的工作原理，因为我尽量避免它:-).您可以在不选择范围的情况下定义和操作范围.此外，明确您所引用的所有内容也是一个好主意.这样，如果您从一张工作表或工作簿转到另一张工作表或工作簿，您就不会迷失方向.试试这个:

I'm not very familiar with how Select works because I try to avoid it as much as possible :-). You can define and manipulate ranges without selecting them. Also it's a good idea to be explicit about everything you reference. That way, you don't lose track if you go from one sheet or workbook to another. Try this:

Option Explicit

Sub CopySheet1_to_PasteSheet2()

    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer
    Dim wksSource As Worksheet, wksDest As Worksheet
    Dim rngStart As Range, rngSource As Range, rngDest As Range

    Set wksSource = ActiveWorkbook.Sheets("Sheet1")
    Set wksDest = ActiveWorkbook.Sheets("Sheet2")

    'Finds last row of content
    CLastFundRow = wksSource.Range("C21").End(xlDown).Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1

    'Copy Data
    Set rngSource = wksSource.Range("A2:C" & CLastFundRow)

    'Paste Data Values
    Set rngDest = wksDest.Range("A21")
    rngSource.Copy
    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'Bring back to top of sheet for consistancy
    wksDest.Range("A1").Select

End Sub

这篇关于VBA 运行时错误 1004“应用程序定义或对象定义错误"选择范围时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！