Using VBA to Copy Invoice Number

[SilverLily]

Hi, this is my first time using VBA and what little I have done has been self taught using online information. I have created an invoice that once you click "enter" it moves all the invoice lines to the solddata worksheet and then clears itself. However, I cannot figure out how to get it to copy the invoice # on each of the lines it copies to the solddata sheet. It is a manual invoice number as we handwrite invoices. Any help would be greatly appreciated. What is in purple below works. What is in red is what I started to write to get the invoice number to copy with the invoice lines and realized I just have no idea how to do it.


Sub Copy_Paste_Below_Last_Cell()

'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim CopyLastRow As Long
Dim DestLastRow As Long

'Set Variables for Copy and Destination

Set wsCopy = Workbooks("UU Inventory Tracker").Worksheets("Invoice")
Set wsDest = Workbooks("UU Inventory Tracker").Worksheets("SoldData")

'1. Find last used row in the copy range based on data in column C

CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "C").End(xlUp).Row

'2. Find first blank row in destination range based on data in column C

DestLastRow = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row


'3 Copy & Paste Data

wsCopy.Range("C14:G" & CopyLastRow).Copy _
wsDest.Range("C" & DestLastRow)

'4 Copy Invoice number & Invoice Date

If SoldData("E").Value >= 1 Then


'Reset Invoice (clear content after copied)

wsCopy.Range("C14:C29").ClearContents
wsCopy.Range("E14:E29").ClearContents
wsCopy.Range("G14:G29").ClearContents
wsCopy.Range("C6").ClearContents





End Sub

 

[bebo021999]

If SoldData("E").Value >= 1 Then

SoldData("E") is not a valid range in VBA

If actually cell E1 of sheet SoldData, use:

If WsDest.range("E1").value >=1 Then