Code won't paste into excel table, always outside it?

[RINCONPAUL]

Can anyone suggest a modification of the below code. It tries to paste to the last blank row, which is the last blank row of a Table. It always pastes to the next blank row outside the table. This pasted data is graphed, and by being within a table, the graph would be dynamic. Pasting outside the table means having to use offset formula and named ranges.....a real pain!

Thanks.

Sub Copy_Race2()
    Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Set copySheet = Worksheets("BOTT")
    Set pasteSheet = Worksheets("GRAPHS")
    copySheet.Range("A2:U2").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    StartTimer
End Sub]

[snb]

That's a known phenomenon for races: they go out of track

[RINCONPAUL]

You guys are having a big gamble on Brexit today, wonder if that'll go off track too

[Aflatoon]

End(xlUp) will stop at the last row of a table, blank or not.

[mdmackillop]

Insert the data before the last row, then move the last row data back to its original position

    Set t = Cells(Rows.Count, 1).End(xlUp).Resize(, 21)
    rw = t.Row
    copySheet.Range("A2:U2").Copy
    t.Insert Shift:=xlDown
    Cells(rw, 1).Resize(, 21).Insert Shift:=xlDown
    Cells(Rows.Count, 1).End(xlUp).Resize(, 21).Cut Cells(rw, 1).Resize(, 2)

[snb]

I don't see any problem ( provided a listobject comprises column(1) ) using:

Sub M_snb()
  sheets("BOTT").Range("A2:U2").Copy sheets("GRAPHS").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End Sub

nor in using

Sub M_snb()
   sheets("GRAPHS").Cells(Rows.Count, 1).End(xlUp).Offset(1).resize(,21)=sheets("BOTT").Range("A2:U2").Value
End Sub

[RINCONPAUL]

Haven't tried mdmackillop's solution yet, but thought I'd post a working example first. Excel Tables are 'something special'! Most people use them to continually add data too. In my case I do that, but then delete all the contents and start over. That's when the issue arises! On sheet BOTT, there is row 2 which updates by a 3rd party input every second. You press Start to launch macro that copies that row over to sheet GRAPHS that contains a Table. The pasted data should end up in the last empty row of the Table (row 2) but ends up in the next 'non Table' rows 3....

After each Race, I delete the rows of the table to start afresh. You can delete all but one blank row, (which makes it a Table after all). If you look on sheet GRAPHS, you can see that I have run the macro for a few seconds and it has pasted after row 2.

[RINCONPAUL]

mdmackillop, trying to adapt your code, without success? Ummm, I notice that after the macro starts copying over to GRAPHS, if I stop the macro, cut & paste the transferred rows outside of the table and lift them up one row, they are reunited with the Table again. Seems so simple, but get it to do that automatically.....not so! I think that's what your code is trying to do?

[mdmackillop]

If you paste/insert before the last row of the table, the table will expand to include the new data. My code the moves the last row to its original position. This last step may not be required in your case, if you can just leave a dummy last row when you clear your old data.

[RINCONPAUL]

OK, I see what you mean. The solution is then a new Sub to clear the last row of the Table and then delete all other rows except the last row.....tricky stuff!

As always, you persevered, and showed your true mettle for understanding.
Something seen as devilishly easy by some, is not always the case?
Cheers

[RINCONPAUL]

Courtesy of 'AlphaFrog', this is the code to clear a Table such that you can re paste data and have it incorporated automatically on next loop series.

Sub DandCTable()
    With Sheets("GRAPHS").ListObjects(1)
        If .DataBodyRange.Rows.Count > 1 Then .DataBodyRange.Resize(.DataBodyRange.Rows.Count - 1).Delete xlShiftUp
        .DataBodyRange.ClearContents
    End With
End Sub

[SamT]

Paul, Do you look at your posts after you submit them?

[RINCONPAUL]

I do Sam, and I know what you're talking about? I edited it (removed all the font color mentions) hit Save....hit save 5 more times....nothing happened!! Not from lack of trying mate.

[RINCONPAUL]

Just tried again!! Will not resubmit edited post

[snb]

That 's an awkward 'solution'
I'd prefer:

Sub M_snb() 
  with sheets("GRAPHS").ListObjects(1).DataBodyRange
     .Cells(1).Offset(Abs(.value <> "")) = "hhh"
  end with
End Sub

[RINCONPAUL]

This was solved, but another issue has arisen? I copy and paste updated data to a Table. In the code I have "xlPasteValues". This works fine, but now I want to paste values and some additional cells with formulas . So I change the code to "xlPasteFormulas", all of a sudden the data is pasted outside (the row below) the last blank row of the Table. It treats the first pasted row as a header row with dropdowns! A new Table, as it were, but not dynamic.

Is their a way to stop this happening?

Thanks so much.

Sub Copy_Race2()
    Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Set copySheet = Worksheets("BOTT")
    Set pasteSheet = Worksheets("GRAPHS")
    copySheet.Range("A14:CS14").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    StartTimer
End Sub

[RINCONPAUL]

Further to above. I don't think this is going to work! I need to paste values for some cells in a row, and other cells as formulas. Can't see a way of doing that?

[SamT]

I believe that Excel Tables have more Methods than Excel Ranges, but that is beyond my expertise.

If the Data Structure is static, that is you always want Values from the same columns and Formulas from the same columns.

Dim NR As Long 'NextRow
With PAsteSheet
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
copySheet.Range("A14:CS14").Copy 
.Cells(NR, "A").PasteSpecial xlPasteValues
copySheet.Range("X14:Z14").Copy 
.Cells(NR, "X").PasteSpecial xlPasteFormulas
End With

[RINCONPAUL]

Thanks Sam. You're approach is logical, just identify cells you want to paste as Values and same for cells you want to paste as Formulas. Why didn't I think of that? That's why you're the Guru. I haven't tried it out yet, as my sheet is interspersed with concurrent groups of "A" and "X", so will take a little time to implement.
Cheers

[SamT]

Paste everything as values, then overwrite with formulas. That eliminates half the groups.

Const FormulaRanges as variant = Array("X14:Z14", "BB14:BD14", "XX14:ZZ14", etc)

Const DestinationColumns As Variant =Array("X", "Y", "Z", Etc)

For i = 0 to Ubound(FormulaRanges)

CopySheet.Range(FormulaRanges(i)).Copy
PasteSheet.Cells(NR, DestinationColumns(i)).PasteSpecial xlPasteFormulas.
Next