Help With selecting/copying and pasting cell values

[Talat]

Hi,

I need help with adapting a VBA code someone helped me with a few years ago.

Basically, I have an excell sheet from which I want to copy values from certain cells and paste into another flat excell file.

The data falls into two types:
1: One off ie invoice date, invoice number, customer number, invoice total
2: A number of possible lines of data like product id, product name, price, invoive line total, VAT code etc

Different invoices will have a differing number of lines of values from type two.


The code I have below does a similar thing, but in this new code, I would like to be able to choose where I copy the
data. Currently a range is copied and then pasted in the same order into the batch file I want to copy the values in.

A slight adaption of this code will work for me if I could specify the order of cells to be copied from in the loop
and that is what I need help with.

Thanks to anyone who is willing to take a shot at this request.

Talât



Sub InvoiceToBatch()
'Copies the current invoice in the INVOICE TEMPLATE sheet of
'this workbook to stocksbatch.xls
Dim myRange As String
Dim Account As String
Dim InvDate As Date
Dim InvNum As Long
Dim InvSheet As Worksheet
Dim BatchSheet As Worksheet
Dim NextRow As Long 'the next available invoice row on the batch sheet
Dim oRow As Long 'row number on BatchSheet
Dim iRow As Long 'row number on InvSheet

Set InvSheet = ThisWorkbook.Worksheets("INVOICE TEMPLATE")

Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\stocksbatch.xls"
Set BatchSheet = ActiveWorkbook.Worksheets("Sheet1")
'NextRow = BatchSheet.Range("D65536").End(xlUp).Row + 1
'oRow = NextRow
oRow = BatchSheet.UsedRange.Rows.Count + 1
iRow = 20


Do

myRange = InvSheet.Range(InvSheet.Cells(iRow, "B"), InvSheet.Cells(iRow, "K")).Address & _
"," & InvSheet.Cells(iRow, "Q").Address
InvSheet.Range(myRange).Copy

'InvSheet.Range(InvSheet.Cells(iRow, "B"), InvSheet.Cells(iRow, "K")).Copy
BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues

InvSheet.Range("E5").Copy 'Account
BatchSheet.Cells(oRow, "A").PasteSpecial xlPasteValues
InvSheet.Range("K2").Copy 'InvNum
BatchSheet.Cells(oRow, "B").PasteSpecial xlPasteValues
InvSheet.Range("F17").Copy 'InvDate
BatchSheet.Cells(oRow, "C").PasteSpecial xlPasteValues

iRow = iRow + 1
oRow = oRow + 1
Loop Until IsEmpty(InvSheet.Cells(iRow, "B")) Or InvSheet.Cells(iRow, "B") = Q
Application.CutCopyMode = False
ActiveWorkbook.Close True 'save changes and close
End Sub