vba - 将单元格值复制到不同的工作簿

Question

首先，如果这个问题已经在其他地方得到了回答，请允许我道歉。我仔细查看了，但找不到任何对我有帮助的东西。

其次，我确信有一种更简单的方法可以做到这一点，但我对 VBA 非常陌生，我只是在尝试自学。

好的，我的工作簿末尾有一张工作表，用于编译上一张工作表中的信息，我想将第 2 行中的所有值复制到我们拥有网络驱动器的另一个工作簿中。

我已经设法让它在同一张工作表上工作，但不能在另一个工作簿上工作(不使用用户窗体)。

它返回该行的错误“无效限定符”单元格(emptyRow，1.Value - DateRaished.Value

下面是我的代码，

Sub CommandButton1_Click()

Dim emptyRow As Long
Dim DateRaised As Long
Dim CustomerName As String
Dim SiteAddress As String
Dim CallReason As String
Dim CustomerOrderNo As Long
Dim InvoiceNo As Long
Dim CovernoteNo As Long
Dim Findings As String
Dim ProductType As String
Dim Supplier As String
Dim Attempts As Long
Dim Condition As String
Dim DateClosed As Long
Dim CreditGiven As String
Dim CreditValue As Long
Dim IssueDays As Long
Dim Comments As String

DateRaised = Cells(2, "A").Value
CustomerName = Cells(2, "B").Value
SiteAddress = Cells(2, "C").Value
CallReason = Cells(2, "D").Value
CustomerOrderNo = Cells(2, "F").Value
InvoiceNo = Cells(2, "G").Value
CovernoteNo = Cells(2, "H").Value
Findings = Cells(2, "I").Value
ProductType = Cells(2, "J").Value
Supplier = Cells(2, "K").Value
Attempts = Cells(2, "L").Value
Condition = Cells(2, "M").Value
DateClosed = Cells(2, "N").Value
CreditGiven = Cells(2, "O").Value
CreditValue = Cells(2, "P").Value
IssueDays = Cells(2, "Q").Value
Comments = Cells(2, "R").Value

Dim WrkBk As Workbook
Dim WrkSht As Worksheet

Set WrkBk = Workbooks.Open("R:\6024 Onsite\COVER NOTE WORKFLOW\Database\Covernote Databse.xlsx")
Set WrkSht = WrkBk.Sheets("Covernote Database")
WrkSht.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

Cells(emptyRow, 1).Value = DateRaised.Value
Cells(emptyRow, 2).Value = CustomerName.Value
Cells(emptyRow, 3).Value = SiteAddress.Value
Cells(emptyRow, 4).Value = CallReason.Value
Cells(emptyRow, 5).Value = CustomerOrderNo.Value
Cells(emptyRow, 6).Value = InvoiceNo.Value
Cells(emptyRow, 7).Value = CovernoteNo.Value
Cells(emptyRow, 8).Value = Findings.Value
Cells(emptyRow, 9).Value = ProductType.Value
Cells(emptyRow, 10).Value = Supplier.Value
Cells(emptyRow, 11).Value = Attemps.Value
Cells(emptyRow, 12).Value = Condition.Value
Cells(emptyRow, 13).Value = DateClosed.Value
Cells(emptyRow, 14).Value = CreditGiven.Value
Cells(emptyRow, 15).Value = CreditValue.Value
Cells(emptyRow, 16).Value = IssueDays.Value
Cells(emptyRow, 17).Value = Comments.Value

WrkBk.Close (SaveChanges = False)

End Sub

如果有人能指出我正确的方向，我会是一个非常快乐的人。

Answer 1

这是因为您尝试将值类型(例如String和Long)变量视为引用 调用其 Value 属性的类型(对象):

Cells(emptyRow, 1).Value = DateRaised.Value

虽然你不能(除非你使用用户定义类型):值类型变量只能按原样访问:

Cells(emptyRow, 1).Value = DateRaised

但您可以简单地编写如下代码:

Option Explicit

Sub CommandButton1_Click()
    Dim emptyRow As Long
    Dim curSht As Worksheet

    Set curSht = ActiveSheet
    With Workbooks.Open("R:\6024 Onsite\COVER NOTE WORKFLOW\Database\Covernote Databse.xlsx").Sheets("Covernote Database")
        emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        .Cells(emptyRow, 1).Resize(, 17).value = curSht.Cells(2, 1).Resize(, 17).value '<-- paste values from originally opened sheet range A2:Q2
    End With
    ActiveWorkbook.Close SaveChanges:=False
End Sub