Excel VBA : Object doesn't support this property or method

Question

尝试运行以下代码时，我收到标题中所述的错误消息。到目前为止，所有通过故障排除和在 stackoverflow 中搜索其他此错误来解决此问题的尝试都没有奏效。相当困惑，不确定如何在没有更多 VBA 知识的情况下进一步解决这个问题(我目前没有，希望很快就能修复。路上有一些书)

为了提供此代码的一些上下文，我试图通过将数据从源工作簿(此代码所在的位置)中的行传输到另一个设计为报告的工作簿中的特定单元格来创建文档。这将需要为源工作簿中的所有行循环。

任何帮助是极大的赞赏。

Sub Transfer()

  Dim sourceDataWb As Workbook
  Dim destinationDataWb As Workbook
  Dim strpath As String
  Dim strfolderpath As String
  Dim numberOfRows As Long, z As Long

  On Error GoTo error_catch

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  'Assign active workbook (this one!) to sourcedatawb
  Set sourceDataWb = ActiveWorkbook

    numberOfRows = sourceDataWb.Range("A1", Range("A1").End(xlDown)).Rows.Count

  For z = 1 To numberOfRows
    ' Open template workbook & assign to destinationdatawb
    Set destinationDataWb = Workbooks.Open("C:\Users\Matthew.Banks\Desktop\client data\output template.xlsx")
    ' Transfer data to output template
    destinationDataWb.Sheets("Inhibit Sheet").Range(C9).Value = sourceDataWb.Sheets("data").Cells(z, 1).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C7).Value = sourceDataWb.Sheets("data").Cells(z, 2).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C8).Value = sourceDataWb.Sheets("data").Cells(z, 3).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(F7).Value = sourceDataWb.Sheets("data").Cells(z, 4).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(F8).Value = sourceDataWb.Sheets("data").Cells(z, 5).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(F9).Value = sourceDataWb.Sheets("data").Cells(z, 6).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C11).Value = sourceDataWb.Sheets("data").Cells(z, 7).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C10).Value = sourceDataWb.Sheets("data").Cells(z, 8).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C21).Value = sourceDataWb.Sheets("data").Cells(z, 9).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C22).Value = sourceDataWb.Sheets("data").Cells(z, 10).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 11).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 12).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 13).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 14).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 15).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 16).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 17).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 18).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 19).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 20).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 21).Value
    destinationDataWb.Sheets("Inhibit Sheet").Range(C23).Value = sourceDataWb.Sheets("data").Cells(z, 22).Value

    ' CREATE THE PATH
    strpath = "C:\" & destinationDataWb.Sheets("Inhibit Sheet").Range("A1").Value & " Report" & ".xlsx"
    ' SAVE
    destinationDataWb.SaveAs Filename:=strpath
    destinationDataWb.Close
    'REPEAT
  Next

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  Exit Sub

error_catch:
  MsgBox "Error: " & Err.Description
  Err.Clear
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Answer 1

我们不能直接使用您提到的选择范围值的工作簿，而是可以通过使用工作表属性如下使用。

numberOfRows = sourceDataWb.Worksheets("Sheet1").Range("A1", Range("A1").End(xlDown)).Rows.Count