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


New Member
Jul 13, 2020
Office Version
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.

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

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:

VBA Code:
Private Sub CommandButtonSubmit_Click()

Application.ScreenUpdating = False

Dim copySheet As Worksheet

Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Submit")

Set pasteSheet = Worksheets("Log")


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

Application.CutCopyMode = False

Application.ScreenUpdating = True


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

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics