Forum Discussion

Eggselling's avatar
Eggselling
Copper Contributor
Dec 31, 2022

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!

developer
excel
Formulas and Functions
Macros and VBA

3 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 10, 2023

    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 Sub
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 04, 2023

    Eggselling Post your code. Use the full text editor and use the "Insert code" button (right-most button) to insert your existing code.

Resources