Can't copy and paste value

KimTDSYN

Why does the paste special for value not work on all cells? I have cells that are populated via a lookup on another file and some cells allow for paste value and some only allow for format.

Also, is there a way to do a copy/paste special for a whole file? I have almost 4k rows of data so individually highlighting all the cells is not really an option. I also would rather not create a new file as I have other files that pull data from it.

KimTDSYN

Can anyone assist on this?

1. I rarely see "value" as a paste special option. It's usually just "format". Why is "value" not always an option?
2. Does anyone know how to do a "global" paste special of value, for an entire file? Creating a new file is not an option.

Kerry St. Thomas

If your row is interrupted with columns that are somehow restricted (locked, column formulas, or anything that could conceivably limit writing to a cell), a paste across the entire row will likely fail. You'd need to either break your pastes into blocks, or move all your restricted columns to the far left or right. So there could be multiple reasons why your individual paste isn't working - it's all basically up to how the sheet is set up. You'll also be limited by your sharing permissions.

Last I was aware, you are only able to copy/paste up to 500 rows. Depending on the source of the data, you may wish to investigate Data Shuttle - this is a way of automating importing data.

KimTDSYN

Thanks. The locked column was the issue. I also found out that column formulas can't be pasted. I had to convert to cell formula first.

The 500 limit of copy and paste is confusing as I was able to select about 2k cells in a column and copy and paste.

The Data Shuttle won't help as I already have the data in the file. I've been manually copying and pasting all my columns. It's just a super pain since I have over 3k rows of data. Ugh.