VBA to paste selection in first empty row within a table

[mick0005]

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?

 

[CalcSux78]

Try searching for "" via the Find function:

Dim rFound As Range
On Error Resume Next
With Sheet1
    Set rFound = .Columns(1).Find(What:="", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
    On Error GoTo 0
    If Not rFound Is Nothing Then
        'found blank cell in column A, paste here
        rFound.PasteSpecial Paste:=xlPasteValues
    End If
End With

 

[mick0005]

Managed to get it working by using a double endup

[COLOR=#333333]Selection.Copy
[/COLOR][COLOR=#333333]Sheets("Store Count & Comp Ref").Range("a1000000").[/COLOR][COLOR=#FF0000][B]End(xlUp).End(xlUp)[/B][/COLOR][COLOR=#333333].Offset(1, 0).PasteSpecial Paste:=xlPasteValues[/COLOR]