VBA extend table to include new row

[SteevJonz]

Hi,

I have a workbook with a macro that extracts information from row 1 and pastes it as values as a new row below the Data table.
I want to add a step to extend the table over the new row, but none of the options I have found so far seem to do it.
My VBA knowledge is negligible (I'm usually SQL-based).

I have pasted the entire VBA below in case it's helpful.

Option Explicit

Sub Submit_Data()

Dim Data_Sheet, Front_Page As Worksheet
Dim Paste_Destination, UserName As Variant
Dim Question, Counter As Integer

'Set variables

Set Data_Sheet = ThisWorkbook.Sheets("Data")
Set Front_Page = ThisWorkbook.Sheets("Front Page")
Paste_Destination = "A" & Data_Sheet.Range("A65536").End(xlUp).Row + 1

UserName = Front_Page.Range("User_Name")

'Ensure required fields are input (User's Name)

If UserName = "" Then
    MsgBox ("Please input your name at the top of the sheet")
    End
End If


'Ensure required field are input (Representative and Call Options Sections)

If Front_Page.Range("B21") = "" Then
    MsgBox ("Rep polite/courteous field requires an input")
    End
End If

If Front_Page.Range("B22") = "" Then
    MsgBox ("All questions answered field requires an input")
    End
End If

If Front_Page.Range("B26") = "" Then
    MsgBox ("Cash/Finance field requires an input")
    End
End If

'Confirmation from user

Question = MsgBox("Are you sure? This will submit the call data and clear the front page", vbYesNo + vbQuestion + vbDefaultButton2)

If Question = vbNo Then End

Application.ScreenUpdating = False

'Copy and paste data from row 1 into the first available row under the headings

Data_Sheet.Range("A1:AC1").Copy
Data_Sheet.Range(Paste_Destination).PasteSpecial xlPasteValues

'Clear information from front page

Front_Page.Range("Clear_Range").ClearContents

Application.ScreenUpdating = True

End Sub

 

[severynm]

I can't tell from your code so far, but is your Data table (where you are pasting the data) formatted as a Table? If it is, any data added in the row (or column) right after the end of the table will be automatically appended to the table. You can test this out just by typing in a cell next to the table - you will see the rows or column automatically extend.

If the destination is a table, this accomplishes the same thing by pasting the data one row below the last row and using the features of Excel Tables to automatically append:

Dim target As ListObject
Set target = SheetNameTableIsIn.ListObjects("NameOfYourTable")
Range("A1:AC1)".Copy target.DataBodyRange.Rows(target.DataBodyRange.Rows.count + 1)

 

[SteevJonz]

I can't tell from your code so far, but is your Data table (where you are pasting the data) formatted as a Table? If it is, any data added in the row (or column) right after the end of the table will be automatically appended to the table. You can test this out just by typing in a cell next to the table - you will see the rows or column automatically extend.

If the destination is a table, this accomplishes the same thing by pasting the data one row below the last row and using the features of Excel Tables to automatically append:

Dim target As ListObject
Set target = SheetNameTableIsIn.ListObjects("NameOfYourTable")
Range("A1:AC1)".Copy target.DataBodyRange.Rows(target.DataBodyRange.Rows.count + 1)

Thanks for your reply.

Yes the table is a Table called Data, but the table isn't extending over the information automatically as I have always had happen previously. I presume it's because I'm not typing in one of the cells, so Excel isn't automatically extending the table.

I tried running the macro, clicking into one cell, and pressing enter (to trigger the extension), but it wouldn't extend the table.

I'd like the data to be in a Table to auto extend some calculated formulas.

 

[severynm]

Strange as it works for me - what happens if you just type in the row below the Table (without running a Macro)? Do you get something like the following? If not, after doing a little research, it may be an Autocorrect setting you have to change?

This is usually my go-to guide for Tables in VBA. You can also explicitly add a row to the end of the Table with SheetName.ListObjects("TableName").ListRows.Add AlwaysInsert:= True

 

[SteevJonz]

Strange as it works for me - what happens if you just type in the row below the Table (without running a Macro)? Do you get something like the following? If not, after doing a little research, it may be an Autocorrect setting you have to change?

View attachment 35607


This is usually my go-to guide for Tables in VBA. You can also explicitly add a row to the end of the Table with SheetName.ListObjects("TableName").ListRows.Add AlwaysInsert:= True

Hi again,

Yes it was very odd for it to not auto-expand after the macro as manual input did trigger the auto expansion correctly.
I haven't run enough macros to know if it was expected or not, but it's good to know that it's not the norm.

I have added in that bit of code and it seems to be working. Thanks so much for your help, it is much appreciated!