Copying cell range (if not blank) to new row in table

[Macca250]

Hello, first post here - I'm not overly familiar with VBA but would really appreciate some help with this task -

Background - I've created a fixed range of cells (on a "Submit" sheet) where a user can quickly input some data as required. The sheet is seven columns (with headings) with enough space for up to 20x rows to be populated as required. By selecting an ActiveX Command Button, the data within this range, provided a certain cell (C6) is not empty, would be copied into a new row on another sheet (the "Log" sheet). Finally, the data on the "Submit" sheet would be cleared and ready for the next round of data to be input. It's intended to be as flexible and simple as possible so you don't have to complete 20x rows every single time.

The issue - I've had several attempts at writing some VBA to accomplish what I need from the Command Button, but to no avail. The closest I've got from looking at other examples on this forum is with the code below. Can anyone help me get this to work? The main challenge appears to be using a table as I've successfully accomplished what I needed to previously by pasting to cells instead of a ListObject.

Private Sub CommandButtonSubmit_Click()

  Application.ScreenUpdating = False

Dim tbl As ListObject
Dim LastRow As Long

Set tbl = Sheets("Log").ListObjects("Log_Table")
LastRow = Sheets("Log").ListObjects("Log_Table").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1, 0).Row

If Sheets("Submit").Empty(Range("C6")) = False Then
Sheets("Submit").Range("A6", "I22").Copy Destination:=tbl.Range(LastRow, "A")
 
 Else: 'This is where it shouldn't copy anything or move onto the next row? Not sure what to put here
  
Sheets("Submit").Range("A6:I22").ClearContents

Application.ScreenUpdating = True
  
MsgBox "Submitted Successfully"

End If

End Sub

In case you're wondering what my previous attempt looked like when pasting to cells instead of a table (which seemed to work), here you go for reference:

Private Sub CommandButtonSubmit_Click()

Application.ScreenUpdating = False


Dim copySheet As Worksheet

Dim pasteSheet As Worksheet



Set copySheet = Worksheets("Submit")

Set pasteSheet = Worksheets("Log")





copySheet.Range("A6:I22").Copy

pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Application.ScreenUpdating = True



copySheet.Range("A6:I22").ClearContents



MsgBox "Submitted Succesfully"



 End Sub

That second one is fine for what I needed, but instead of going into the table, it obviously pastes below it.

I appreciate this may look like a poor effort to some (apologies in advance!) but any help is appreciated