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

Macca250

New Member
Joined
Jul 13, 2020
Messages
1
Office Version
365
Platform
Windows
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
  
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:

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





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

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

Threads
1,105,930
Messages
5,508,172
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top