Solved: Inserting dynamic range into new table

[energizek]

Hi,

I have created a table shell on sheet2 of my workbook and I want a dynamic range (a column vector of varying length) from sheet1 to populate column A of the table shell in sheet2. The problem is that the length of the range is dynamic (there may be 20-200 rows of data) and in many cases I need the table shell in Sheet2 to expand (insert rows and maintain formatting) to accomodate the dynamic list.

How can I copy and paste a dynamic list between worksheets and have Excel automatically expand the number of cells to fit my needs?

Thanks!
Katie

[mdmackillop]

Something like

Option Explicit
Sub InsertRows()
Dim Rws As Long
Rws = Range("trial").Rows.Count
Sheets(2).Cells(2, 1).Resize(Rws, 6).Insert shift:=xlDown
Range("trial").Copy
Sheets(2).Cells(2, 1).PasteSpecial xlValues
Application.Goto Sheets(2).Cells(2, 1)
End Sub

[energizek]

THank you very much! I was able to make a few modifications and use the code you sent. Much appreciated!