Excel Forum

Code: Select all

Sub Order()
 Dim nrow As Integer
 Dim lastrow As Long
 lastrow = Sheets("order").Range("C" & Rows.Count).End(xlUp).row + 1
Sheets("Order").Range("c" & lastrow).Value = Sheets("invoice").Range("g8").Value
Sheets("order").Range("D" & lastrow).Value = Sheets("invoice").Range("G11").Value
Sheets("order").Range("U" & lastrow).Value = Sheets("invoice").Range("B15:B24").Value
Sheets("order").Range("Y" & lastrow).Value = Sheets("invoice").Range("F15:F24").Value

MsgBox "Completed"

    End Sub

Code: Select all

'Other code
Dim iv As Worksheet, i As Integer
Set iv = Sheets("invoice")
With Sheets("order")
    i = Application.Count(iv.Range("a15:a24"))
    lastrow = .Range("c" & .Rows.Count).End(xlUp).row + 1
    .Range("c" & lastrow).Resize(i).Value = iv.Range("g8").Value
    .Range("d" & lastrow).Resize(i).Value = iv.Range("g11").Value
    '...Other code...
    .Range("u" & lastrow).Resize(i).Value = iv.Range("b15").Resize(i).Value
    .Range("y" & lastrow).Resize(i).Value = iv.Range("f15").Resize(i).Value
End With
MsgBox "Completed"

Code: Select all

Sub Order()
 Dim nrow As Integer
 Dim lastrow As Long
 Dim cItem As Long
 Dim myRange As Range
 lastrow = Sheets("order").Range("C" & Rows.Count).End(xlUp).row + 1
 Set myRange = Sheets("invoice").Range("B15:B24")
 cItem = Application.WorksheetFunction.CountA(myRange)
Sheets("Order").Range("C" & lastrow, "C" & lastrow + cItem - 1).Value = Sheets("invoice").Range("g8").Value
Sheets("order").Range("D" & lastrow, "D" & lastrow + cItem - 1).Value = Sheets("invoice").Range("G11").Value
Sheets("order").Range("E" & lastrow, "E" & lastrow + cItem - 1).Value = Sheets("invoice").Range("B8").Value
Sheets("order").Range("F" & lastrow, "F" & lastrow + cItem - 1).Value = Sheets("invoice").Range("B9").Value
Sheets("order").Range("G" & lastrow, "G" & lastrow + cItem - 1).Value = Sheets("invoice").Range("B12").Value
Sheets("order").Range("H" & lastrow, "H" & lastrow + cItem - 1).Value = Sheets("invoice").Range("B10").Value
Sheets("order").Range("M" & lastrow, "M" & lastrow + cItem - 1).Value = Sheets("invoice").Range("B11").Value
Sheets("order").Range("Q" & lastrow, "Q" & lastrow + cItem - 1).Value = Sheets("invoice").Range("I29").Value
Sheets("order").Range("R" & lastrow, "R" & lastrow + cItem - 1).Value = Sheets("invoice").Range("G10").Value
Sheets("order").Range("S" & lastrow, "S" & lastrow + cItem - 1).Value = Sheets("invoice").Range("G9").Value
Sheets("order").Range("U" & lastrow, "U" & lastrow + cItem - 1).Value = Sheets("invoice").Range("B15:B24").Value
Sheets("order").Range("Y" & lastrow, "Y" & lastrow + cItem - 1).Value = Sheets("invoice").Range("F15:F24").Value
  
MsgBox "Completed"
    
End Sub