Search code examples
excelvba

Create new cell spaces in record sheet every new order

I am trying to make cell spaces every time Saved Invoices go into record sheet.

Instead the last cell of item and qty get overwritten by the save.
And it didn't go as a whole one order if I try to sort it from individual table.

I am trying to make something like this:
enter image description here

The full code

Sub Save()
Dim SimpanInvoice As Object
Set SimpanInvoice = Sheet3.Range("A20000").End(xlUp)

If Sheet4.Range("Invonomer").Value = "" _
  Or Sheet4.Range("Invotgl").Value = "" _
  Or Sheet4.Range("Invoto").Value = "" _
  Or Sheet4.Range("Invoalamat").Value = "" Then

    Call MsgBox("Harap isi data dengan lengkap", vbInformation, "Isi Data")

Else
    SimpanInvoice.Offset(1, 0).Value = Sheet4.Range("Invonomer").Value
    SimpanInvoice.Offset(1, 1).Value = Sheet4.Range("Invotgl").Value
    SimpanInvoice.Offset(1, 2).Value = Sheet4.Range("Invoto").Value
    SimpanInvoice.Offset(1, 3).Value = Sheet4.Range("Invoalamat").Value
    SimpanInvoice.Offset(1, 4).Value = Sheet4.Range("Keterangan").Value
    SimpanInvoice.Offset(1, 5).Value = Sheet4.Range("item1").Value
    SimpanInvoice.Offset(1, 6).Value = Sheet4.Range("Qty_1").Value
    SimpanInvoice.Offset(2, 5).Value = Sheet4.Range("item2").Value
    SimpanInvoice.Offset(2, 6).Value = Sheet4.Range("Qty_2").Value

    Call MsgBox("Data SuratTagihan Telah Di Simpan", vbInformation, "Data Surat Tagihan")

    Sheet4.Range("Invonomer").Value = ""
    Sheet4.Range("Invotgl").Value = ""
    Sheet4.Range("Invoto").Value = ""
    Sheet4.Range("Invoalamat").Value = ""
    Sheet4.Range("Keterangan").Value = ""
    Sheet4.Range("item1").Value = ""
    Sheet4.Range("Qty_1").Value = ""
    Sheet4.Range("item2").Value = ""
    Sheet4.Range("Qty_2").Value = ""

End If
End Sub
Solution

  • Your ColA is not fully populated, so you could add a loop to make sure the row you're working on is empty

    Dim SimpanInvoice As Object

Set SimpanInvoice = Sheet3.Range("A20000").End(xlUp).Offset(1)
'make sure the row we're on is completely empty (not just empty in ColA) 
Do While Application.CountA(SimpanInvoice.EntireRow) > 0
    Set SimpanInvoice = SimpanInvoice.Offset(1) 'next row down 
Loop
'start populating the row
SimpanInvoice.Value = Sheet4.Range("Invonomer").Value
SimpanInvoice.Offset(0, 1).Value = Sheet4.Range("Invotgl").Value
'etc etc