Appending a row to the bottom of a table [SOLVED]

[dronka]

I would like to create a macro that copies a row of data on one sheet and appends it to the bottom of a table on another sheet. The only way I can figure out how to do it is to insert the copied row above the first row of data in the table (because I know that will always be row 2 of the spreadsheet). How do you get Excel to copy the row to the bottom of the table when the location of the bottom of the table changes every time a new row is added?

 

[Luke M]

To find the last row with data, we typically do something like:

Range("A65536").End(xlUp)

if you wanted to paste to the next empty line, we'll need to do an [code]Offset

, so the whole code might look something like

[pre]Worksheet("Sheet1&#34.Range("A2:E2&#34.Copy _
Worksheet("Sheet2&#34.Range("A65536&#34.End(xlUp).Offset(1,0)[/code][/pre]

 

[dronka]

Thanks, Luke. Great idea. To do that, though, I'd need to make sure that every cell in column A in the table has data in it, right? Otherwise I'm pasting into a row that already has data in it.

 

[dronka]

Luke - I tried the code with the following modifications:


Worksheet("Entry").Range("2:2").Copy _

Worksheet("Sales").Range("A65536").End(xlUp).Offset(1, 0)

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False


The idea here is that I'm copying from Entry row 2 and pasting to the bottom of the table on Sales.


I get this error when I run: "Compile Error: Sub or Function not defined."


It highlights "Worksheet" in the second line of code above.


Can you see what the problem might be?

 

[dronka]

I think I figured out the error.


But now I have the problem that it pastes the row at the bottom of the table, but the table doesn't automatically accept it as another row of data. So it's pasted below the table, but not part of the table. Hmmm... thoughts?

 

[jeffreyweir]

Maybe the auto-expand feature is turned off.


What happens if you manually type something in the last row?


See http://www.mrexcel.com/forum/excel-questions/513197-how-turn-table-auto-expand-feature-back.html

 

[NARAYANK991]

Hi David ,


Almost all that you need to know about using Excel tables in VBA is in here :


http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp


There is a topic in this forum which has some answers :


http://chandoo.org/forums/topic/vba-add-new-row-to-end-of-table-with-yesterdays-date


Googling this throws up a lot of results :


http://stackoverflow.com/questions/8295276/excel-vba-function-or-sub-to-add-new-row-and-data-to-table


http://stackoverflow.com/questions/12297738/add-new-row-to-excel-table-vba


Narayan

 

[jeffreyweir]

Good links. Given we are dealing with tables aka listobjects, then instead of using the Range("A65536").End(xlUp) method, it's probably safer to use the listobject properties to find the last row, or to add another row at the end. Here's some sample code, that does these things for an excel table that is selected:

[pre]

Sub AddressListObjects()
Dim lo As ListObject
Dim lr As ListRow
Set lo = Selection.ListObject

With lo

'Find the last row
Debug.Print .ListRows.Count + .DataBodyRange.Row - 1

'Add a row to the bottom and do something with it
Set lr = .ListRows.Add
lr.Range(1, 2).Value = "test"
End With

End Sub

[/pre]

using the .ListRows.Add method will also ensure that your table auto-expands.

 

[dronka]

Thank you both for your help on this. I tried Jeff's code and it works like a charm!