Range syntax question

[Talat]

How do I set a range for something like this:

myRange=invsheet (iRow "A"), (iRow "D"), (iRow "H"), (iRow "K"). copy

BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues


where the range is made up of non contiguous cells.

I would really appreciate some help on the syntax.

Thanks.

Talât

 

[Jonmo1]

Without the row Variable, it would be like this

invsheet.Range("A1, D1, H1, K1").Copy
BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues

 

[Jonmo1]

To use the Row Variable, I would suggest something like this

Intersect(invsheet.Range("A:A, D:D, H:H, K:K"), invsheet.Rows(iRow)).Copy

 

[Talat]

Thanks jonmo1

That works well, but now I am having problems with pasting the values. I want values from each of the cells in the range to be pasted in different columns of the same row of teh batchSheet, not necessarily pasted in the same order they were copied.

The following script gives me an error and I don't know where the syntax is failing me.

----------------ooooo-------------------------

Intersect(InvSheet.Range("B:B, F:F, C:C, D:D, J:J, K:K"), InvSheet.Rows(iRow)).Copy
'InvSheet.Range(myRange).Copy

BatchSheet.Cells(oRow, "A", "B", "E", "F", "G", "I").PasteSpecial xlPasteValues

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

etc etc etc

------------ oooo------------------------------------------

Can you help me with that bit as well? It will be much appreciated.

Thanks .

Talât

 

[Jonmo1]

You can't paste to non contiguouse cells.
Even when doing it by hand you can't.

Try something like this

Dim Cranges As Variant, Pranges As Variant
Dim iRow As Long, oRow As Long, i As Long
 
iRow = 'Your current code to define iRow
oRow = 'Your current code to define oRow
 
Cranges = Array("B", "F", "C", "D", "J", "K")
Pranges = Array("A", "B", "E", "F", "G", "I")
 
For i = LBound(Cranges) To UBound(Cranges)
    InvSheet.Cells(iRow, Cranges(i)).Copy
    BatchSheet.Cells(oRow, Pranges(i)).PasteSpecial xlPasteValues
Next i

 

[Talat]

Thank you jonmo1.

This has now enabled me to complete the code so that it does what I want it to do. It probably is not the cleanest of scripts and I am sure someone with more VBA knowledge can do it much neater and in fewer lines..... but it works for me.

I am putting the code below, in case it is of help to others who might be struggling like me. Once again, many thanks. It is much appreciated that there are people like you around willing to put aside time to help others.

Talât

Sub FAPBatchfile()

   'Copies data from the current invoice in the INVOICE TEMPLATE sheet of
   'this workbook to FAPBAtchfile.xls
   Dim myRange      As String
   Dim Account       As String
   Dim InvDate       As Date
   Dim InvNum        As Long     'invoice number
   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
   Dim Cranges       As Variant
   Dim Pranges       As Variant
   Dim i             As Long
      
   
   Set InvSheet = ThisWorkbook.Worksheets("INVOICE TEMPLATE")
   
    Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\FAPBatchfile.xls"
    Set BatchSheet = ActiveWorkbook.Worksheets("FAPBatch File")
    oRow = BatchSheet.UsedRange.Rows.Count + 1
    iRow = 20
       
    Do
           
    Intersect(InvSheet.Range("B:B, F:F, C:C, D:D, J:J, K:K"), InvSheet.Rows(iRow)).Copy
            
    Cranges = Array("B", "C", "D", "F", "J", "K")
    Pranges = Array("E", "F", "G", "H", "I", "J")
 
    For i = LBound(Cranges) To UBound(Cranges)
    InvSheet.Cells(iRow, Cranges(i)).Copy
    BatchSheet.Cells(oRow, Pranges(i)).PasteSpecial xlPasteValues
      
    
    InvSheet.Range("E5").Copy     'Account
    BatchSheet.Cells(oRow, "C").PasteSpecial xlPasteValues
    InvSheet.Range("K2").Copy    'InvNum
    BatchSheet.Cells(oRow, "A").PasteSpecial xlPasteValues
    InvSheet.Range("F17").Copy    'InvDate
    BatchSheet.Cells(oRow, "B").PasteSpecial xlPasteValues
    
    
    InvSheet.Range("B6").Copy    'Customer name
    BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues
               
    Next i
    
    iRow = iRow + 1
    oRow = oRow + 1
      
    Loop Until IsEmpty(InvSheet.Cells(iRow, "B")) Or InvSheet.Cells(iRow, "B") = Q
        
    InvSheet.Range("E5").Copy     'Account
    BatchSheet.Cells(oRow, "C").PasteSpecial xlPasteValues
    InvSheet.Range("K2").Copy    'InvNum
    BatchSheet.Cells(oRow, "A").PasteSpecial xlPasteValues
    InvSheet.Range("F17").Copy    'InvDate
    BatchSheet.Cells(oRow, "B").PasteSpecial xlPasteValues
    
    InvSheet.Range("B6").Copy    'Customer name
    BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues
    
    InvSheet.Range("C38").Copy
    BatchSheet.Cells(oRow, "F").PasteSpecial xlPasteValues
    
    InvSheet.Range("D39").Copy
    BatchSheet.Cells(oRow, "G").PasteSpecial xlPasteValues
    
    InvSheet.Range("F38").Copy
    BatchSheet.Cells(oRow, "H").PasteSpecial xlPasteValues
    
    InvSheet.Range("K38").Copy
    BatchSheet.Cells(oRow, "J").PasteSpecial xlPasteValues
    
    InvSheet.Range("K44").Copy    'Invoice total
    BatchSheet.Cells(oRow, "K").PasteSpecial xlPasteValues
    
    
    InvSheet.Range("B38").Copy    'FMP Account Code
    BatchSheet.Cells(oRow, "E").PasteSpecial xlPasteValues
    
     
    Application.CutCopyMode = False
   ActiveWorkbook.Close True           'save changes and close
End Sub