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

Macca250

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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,127,698
Messages
5,626,375
Members
416,176
Latest member
Dyl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top