Expand a table by copying new rows in the end

[philoly]

​

I am new in VBA coding and usually I use the recording of macros, but now I need your help because I am stuck.
I have a range C23:K27 with the name (Import_to_Database) in a specific sheet (Copy_to_Database).
I would like to assign a macro to a button in order to copy (special as values) everytime, this range to the bottom of a table with the name ( Black_Database), which is placed in another sheet with the name (Black_Database) Table`s range (A:I) and last step to refresh all the excel workbook.
This is what I tried, but every time, it replaces the same range in the data of the (Black_Database). I don`t know how to write the code in order to add it in the end of the table and expand the table.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub Import_to_database()
'
' Import_to_database Macro
'

'
Sheets("Copy_to_Database").Select
Range("Table3").Select
Selection.Copy
Sheets("Black_Database").Select
ActiveWindow.SmallScroll Down:=123
Range("A137").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.RefreshAll
Sheets("Pivot_Tables").Select
End Sub</code>


Can anybody help me?

Thank you in advance.

 

[Dryver14]

try changing this line Range("A137").Select to Range("A1").End(xlDown).Offset(1, 0).Select

 

[philoly]

Thank you very much Dryver14, it was really helpfull. It works perfectly.