CODEWITHSUNDEEP
vbaexcelvariables
Peter Peazy Littlefield
Peter Peazy Littlefield

Reputation: 31

Copy cell values to different Workbook

Firstly, let me apologise if this question has already been answered somewhere else. I had a good look but couldn't find anything that would help me.

Secondly, I'm sure there is a far more simple way to do this but I'm very new the VBA and I'm just trying to teach myself as I go along.

Ok, so I have a sheet at the end of my workbook that compiles information from the previous sheet and I want to copy those values that are all in row 2 to another workbook that we have a network drive.

I've managed to get this to work on the same sheet but not to another workbook (without using a userform).

It comes back with the error 'Invalid Qualifier' for the line Cells(emptyRow, 1.Value - DateRaised.Value

Here is my code below,

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

If anyone can point me in the right direction I'd be a very happy man.

Upvotes: 2

Views: 104

Answers (1)

user3598756
user3598756

Reputation: 29421

it's because you're attempting to treat value types (like String and Long) variables as if they were reference type (objects) ones calling their Value property:

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

while you can't (unless you use User Defined Types): value type variables can be only accessed as they are:

Cells(emptyRow, 1).Value = DateRaised

but you can simply code like follows:

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

Upvotes: 2

Related Questions