PDA

View Full Version : Code won't paste into excel table, always outside it?



RINCONPAUL
06-23-2016, 03:15 AM
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
06-23-2016, 03:23 AM
That's a known phenomenon for races: they go out of track ;)

RINCONPAUL
06-23-2016, 03:38 AM
You guys are having a big gamble on Brexit today, wonder if that'll go off track too:anyone:

Aflatoon
06-23-2016, 04:25 AM
End(xlUp) will stop at the last row of a table, blank or not.

mdmackillop
06-23-2016, 05:35 AM
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
06-23-2016, 06:34 AM
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
06-23-2016, 01:45 PM
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
06-23-2016, 02:18 PM
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
06-23-2016, 02:43 PM
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
06-23-2016, 03:12 PM
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 :sleuth:

RINCONPAUL
06-23-2016, 04:57 PM
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
06-23-2016, 05:10 PM
Paul, Do you look at your posts after you submit them?

RINCONPAUL
06-23-2016, 05:14 PM
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
06-23-2016, 05:20 PM
Just tried again!! Will not resubmit edited post :igiveup:

snb
06-23-2016, 11:41 PM
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
06-27-2016, 08:13 PM
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
06-27-2016, 08:43 PM
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
06-28-2016, 06:59 AM
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
06-28-2016, 11:42 AM
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
06-28-2016, 06:05 PM
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

RINCONPAUL
06-28-2016, 06:14 PM
Now we're talkin' champ. Good one!!:clap2: