PDA

View Full Version : [SOLVED] Excel VBA: Copy cell values to next empty row in table when row contains formulas.



JubDown
01-14-2019, 08:13 AM
So I have a table on a worksheet that I'm trying to copy values into. The later parts of the table contain formulas (columns C:N), but A & B do not, which is what I'm trying to copy. However, every time I run my code, it pastes the values into the next empty row outside of the table (which still contains the same formulas), and even after adjusting the table to a different row range, it still pastes outside of it. Is there any way to fix it or modify the code to better suit my task? This is the code:


Worksheets("Inventories").Range("A9:B9").Copy
Worksheets("Inventories").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

or the second variant that I have utilized


[A9:B9].Copy r(r.Rows.Count + 1, 1)
Application.CutCopyMode = False

JubDown
01-14-2019, 10:20 AM
I fixed it by implementing this code in its stead.


Sheets("Inventories").Range("A9:B9").Copy
Sheets("Inventories").Range("A1000000").End(xlUp).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues