Copy text not formula between two worksheets

[Southernw2002]

I am attempting to automatically copy selected rows to a new worksheet and all works well but for one thing.
Some of the sources cells have formulas imbedded and when the current systems does the copy I get a #REF error in the new worksheet cell. So what I want to do is copy the row as text only.

I have tried the main answer that seems to appear (changing .paste to .pastespecial )but it doesnt seem to work... so suggestions ???


The current code is......

Please Login or Register  to view this content.

Many thanks (and a big Seasonal Cheer)

[abousetta]

Maybe try:

Please Login or Register  to view this content.

abousetta

[Southernw2002]

That was quick .. many thanks Abousetta....unfortunately it just seems to send the routine into a loop and I have to 3 finger the pc to stop it

[abousetta]

There is no loop in the line of code I posted. So there is no reason for that to be the problem. Try it in a separate sub and see what happens, else post a sample workbook.

[Southernw2002]

Hi again .. bad choice of words on my part.. but when I do enter your suggestion and run the code the pc locks and excel stops responding

[abousetta]

I don't see any reason for your computer to hang from that line of code. I will need to see your workbook or a dummy copy of it to give further advice.

abousetta

[Southernw2002]

test excel.zip


Hi there .. attached is a cut down version of the worksheet
Basically data is entered in the year sheet (2012, 2013 etc)
SORTED is a summary of the jobs that don’t have a entry in the CLEARED Column
Entering a name and a year reference in the yellow cells on the SORTED sheet.. then pressing REPORT makes it all happen
The problem results from having a auto number formula in the F column on each of the year sheets
To show , 2012 doesn’t have the formula, 2013 does .. and 2013 gets the #REF error.. or as a quirk in this one someodd numbers
Anything you can suggest would be appreciate