Range syntax question

Talat

New Member
Joined
Sep 17, 2004
Messages
33
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Without the row Variable, it would be like this

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Sep 17, 2004
Messages
33
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
You can't paste to non contiguouse cells.
Even when doing it by hand you can't.

Try something like this

Rich (BB code):
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

New Member
Joined
Sep 17, 2004
Messages
33
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 :)

Code:
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
 

Forum statistics

Threads
1,082,017
Messages
5,362,697
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top