excel cannot complete this task when paste values from temp sheet

**squarahollier** · 05-08-2014, 09:01 AM

Hi all

I'm running a macro in Excel 2010 that filters unique values, does v lookups and simple calculations then pastes a table from a temporary sheet into a permanent sheet. I am repeating this process 14 times in total. However, it will only work for a maximum of two iterations before I get the error above. It appears to be at paste values that it breaks.

Please Login or Register  to view this content.

any insight into how I can fix this?

thanks a lot

**GeneralDisarray** · 05-08-2014, 09:28 AM

usually you will be better off by equating the values of a particular range. You Are selecting 9 columns, but i would imagine you don't need all of it.

Try this:

Please Login or Register  to view this content.

-----------
Using Select/Copy/PasteSpecial is not impossible, and I realize I didn't really answer your exact question. Just usually you never have to do that - making use of range variables is sooooooooo much simpler once you are introduced to it.

Also, using sheetName.usedRange is extremely handy - but only if your worksheet in this case contains only the items you want (which, would be a good principle to design things around). That being said, if you have more stuff on the worksheet outside of the range you want to copy over, we can alter that statement to only capture the parts you need to copy.

**squarahollier** · 05-09-2014, 05:05 AM

Used range was in fact what I was looking for!

Thanks ever so much. I used this along with another solution from stackoverflow

http://stackoverflow.com/questions/2...rom-temp-sheet

**GeneralDisarray** · 05-09-2014, 09:10 AM

Good to hear, please mark this question as "Solved"

To mark your thread solved do the following:

-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved