VBA extend table to include new row

SteevJonz

New Member
Joined
Mar 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.



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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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:

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

SteevJonz

New Member
Joined
Mar 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

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

Board Regular
Joined
Jan 8, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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?

TableRowAdd.png



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
 
Solution

SteevJonz

New Member
Joined
Mar 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

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