Forum Discussion
Using Macros to add to a table
Keyboard Shortcut: Ctrl+y
'
Dim ws As Worksheet
Set ws = ActiveSheet
Table_Name = InputBox("Table2")
Dm tbl As ListObject
Set tbl = ws.ListObjects(Table_Name)
tbl.ListRows.Add
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearFormats
Range("A5").Select
Selection.Delete Shift:=xlUp
Range("A6").Select
Selection.Delete Shift:=xlUp
Range("A7").Select
Selection.Delete Shift:=xlUp
Range("A8").Select
Selection.Delete Shift:=xlUp
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range(Table2).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub
••••ˇˇˇˇSSSÅHey everyone!
Currently going mad trying to using macros/VBA to quickly add data to a table.
I have to take data from an internet catalogue, clean it, and add it into an excel table. I recorded a macro so that the steps could be done for me. my only issue is that I cant set up the macros so that it adds the data to a final row. Instead, it deletes the data that already exists in the final row. This means it works for me exactly once. Does anyone know a way around this?
Thank you!
3 Replies
Eggselling I think this is what you are looking for?
Option Explicit Sub InsertIntoTable() Dim ws As Worksheet Set ws = ActiveSheet Table_Name = InputBox("Table2") Dim tbl As ListObject Set tbl = ws.ListObjects(Table_Name) Range(Selection, Selection.End(xlDown)).Select Selection.ClearFormats Range("A5").Select Selection.Delete Shift:=xlUp Range("A6").Select Selection.Delete Shift:=xlUp Range("A7").Select Selection.Delete Shift:=xlUp Range("A8").Select Selection.Delete Shift:=xlUp Range("A4").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy 'This pastes in the left-most cell, immmediately below the last row of the table 'Excel then automatically expands the table with the # of rows you pasted tbl.ListRows(tbl.ListRows.Count).Range.Offset(1).Resize(1, 1).Paste End SubEggselling Post your code. Use the full text editor and use the "Insert code" button (right-most button) to insert your existing code.
- Thanks Jan, just posted!
