Paste to first empty row in a table (NOT at the end of the table range)

Hi all -

I have some code which I actually created just by recording a macro and then making some slight modifications to it. One of the primary functions of it is that it takes a range of data from one sheet and then appends it to the bottom of a similar range of data on another sheet.

The behavior I need is that it will find the first empty cell in column A and then begin to paste what is in the clipboard there.

This code is working for me to do that. The problem is that I want to make the destination location into a table. When it is in a table (and the table range extends below the last row of data) my code doesn't work. It ends up pasting the data at the row where the table range ends. I need it to paste it where the data ends, not the table range.

Here is the portion of my code that is relevant I think.

    Selection.Copy    
    Sheets("Store Count & Comp Ref").Select
    Range("a1000000").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues

Can this be modified so that it will do what I need when I have the destination being formatted as a table?

Originally Posted by :) Sixthsense :)

Use double EndUp for finding the last used row of the Table

Selection.Copy
Sheets("Store Count & Comp Ref").Range("a1000000").End(xlUp).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

BOOOOM!! How simple! I should have thought of that.

Works perfect. Thanks SixthSense!