VBA pasting information from a form onto the next open line in a table-CodePudding

I have the following form:

When user clicks the "Submit Adjustment" button, I want the information in the yellow boxes (plus the date on that line) entered into the following table

Here's the code I'm using:

Sub LOG_CHG()
  Sheets("ENTER CHG").Range("B8:I8").Copy
  Sheets("CHANGE LOG").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
    PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False

  MsgBox ("Your adjustment has been logged.")
   Range("C8:I8").Select

   Selection.ClearContents
   Range("C8").Select

End Sub

This is happening somewhat successfully, but sometimes it gets lost and pastes at the bottom of the table or in random places.

How can I make sure it pastes the information in the next available row?

CodePudding user response：

Sheets("CHANGE LOG").Cells(Rows.Count, "A").End(xlUp).Offset(1)

This line means Excel is checking sheet "change log" from last Excel line in column A up until it finds any symbol in cell. And offset(1) means 1 row down. For example:

You have something written in A32(space, number or letter), but from A33 to A1000000 row is nothing --> so your code will paste everything in A33.

In other words Select A1000000 and press Ctrl UP.

CodePudding user response：

As you are using a table on the log-sheet you can access it via the listobjectwhich is pretty easy to program against.

Public Sub log_chg()

Dim rgSource As Range
Set rgSource = ThisWorkbook.Worksheets("Enter chg").Range("B8:I8")

Dim loChangeLog As ListObject
Set loChangeLog = ThisWorkbook.Worksheets("Change log").ListObjects(1)

Dim lrTarget As ListRow
Set lrTarget = lo.ListRows.Add

lrTarget.Range.Value = rgSource.Value

End Sub

No need to check for the last row or anything. ListRowis always appended to the end of the table.

As the ranges of the source and the target are of same size you can write the value like shown in the code.

What you should do before testing: delete all the empty rows of the log table.