exporting activesheet as values getting #name error

[Ravana]

Hi all,

I am using below code to export the activesheet as values however getting #NAME error whereever there is UDF. How to export the even UDF cells as values.

Please help.

Please Login or Register  to view this content.

[Richard Buttrey]

Where's the UDF?

You need to upload the workbook so that we can see your problem in context.

[romperstomper]

Don't copy the sheet to a new workbook. Add a new workbook then copy and paste special the cells to the new workbook.

[Doc.AElstein]

Hi Ravana

I am using below code to export the activesheet as values however getting #NAME error whereever there is UDF. How to export the even UDF cells as values.
.........

_1 ) What rorya said, ( which is probably the easiest )
or
_2) maybe this alternative. This a lot more complicated, but as Richard suggested, we are guessing a bit without knowing exactly where your things are and as I do here it is always better anyway to reference your Workbooks and Worksheets properly.
You might also want to consider changing your Thread Title also to something like Create new Workbook containing Values from ActiveSheet, as we are guessing from your code that that is what you actually want to do.
I think also Richard may have been suggesting that you check where the UDF is , what it does etc etc.. as you may or may not be wanting to have that in your new Workbook. We do not Know
_ So anyways.......
My alternative:

So instead of this:

Please Login or Register  to view this content.

_...............

Do this:

Please Login or Register  to view this content.

Alan

[Ravana]

@Doc,

I tried your alternative. I do not get any error but I am missing all the Formats. I do not want to lose the formats.

[Doc.AElstein]

@Doc,

I tried your alternative. I do not get any error but I am missing all the Formats. I do not want to lose the formats.

Oops, sorry... I had guessed that you probably wanted to keep Formats, but I chose the wrong Method

Change
ActiveSheet.Cells.Clear
to
ActiveSheet.Cells.ClearContents

Appologise, my mistake

Clear __Method clears almos everything

ClearContents __ Method just empties the Cells of their contents

Alan

( Edit: I Have edited the code in post #4 )

[Ravana]

@Doc,

I used the changed code

Please Login or Register  to view this content.

Then I am getting error highlighting the following line of code

Please Login or Register  to view this content.

[Doc.AElstein]

@Doc,

I used the changed code

Please Login or Register  to view this content.

Then I am getting error highlighting the following line of code

Please Login or Register  to view this content.

The change should not have done anything else than ensuring that your Formats remain.
So First, put back to .Cells.Clear and see if you get no error as was previously the case.
_......................................


I have re run that code on a File and it is working OK by me


_1 ) What error do you get ?

_2) Can you upload your File?

Alan

[Ravana]

@Doc,

Sorry for late reply. I was out-of-station so could reply promptly.

Please see the attached file where I am still getting error.

[Doc.AElstein]

Hi Ravina.
I think I see roughly see the problem
But i do not know of a simple solution

It does not seem to like your Merged cells. ( Most Things and people don’t !! )
Ido not know why this gives an error in this case.

But
A Workaround:....

I tried Rory’s way and that did not work and errored for the same reason

I experimented a bit untill I got codes to work.
I got parts of the codes from a macro recording and just modified and experimented etc...

The codes seem to work. But I got very confused as to why that simpler ways did not work.
I am sure there may be a better way.


I do not really understand what is going on here. Sorry.
I think the merged cells may be the problem. I and many people avoid them. In VBA they cause lots of problems.
But maybethe codes will do for now.

Alan

_.....................

So Here a couple of codes. They appear to work on the file you gave. I understand what they are doing. But I do not understand why I had to do it in such a complicated way to get it to work.

I expect you may have further pproblems if you change your original File Format. Especially if you continue to use Merged cells.

Please Login or Register  to view this content.

[romperstomper]

Or just disable calculation:

Please Login or Register  to view this content.

[Doc.AElstein]

Or just disable calculation:
.......

Don’t think so ??

_...

These still give the same original error... #NAME error wherever there is UDF
And you have formulas and any attempts to copy and paste values leads to another error again

Please Login or Register  to view this content.

Still seem to need a fiddly workaround to stop those F__king Merged Cells putting a spanner in the works

Another one that seems to work

Please Login or Register  to view this content.

[romperstomper]

Works fine for me with the sample workbook.

[Doc.AElstein]

XL 2007 .
I get this

UDFProb.JPG


In XL2010 seems to work

strange
Never mind,
see what the OP says

[Ravana]

@Doc, exactly, thats weird.

Rorya's code seem to work for me. Just disable calculation, how on earth could have thought about that.

@Rorya, you are a gem. Thanks for the time and the help.
@Doc, thanks again for taking some time to help.

[Doc.AElstein]

Hi Ravana.

I see my mistake. ! Sorry !

In fact in Xl2007 and XL 2010 it did not work for me the first time around. It worked the second time around, but only because the original Sheet had changed to values !!!
This was because I was running the code in a sheet module and so these lines

Please Login or Register  to view this content.

Would always work on the original sheet, as I had the code in the sheet code module from that sheet.

It is very wise always to be explicit when referring to ranges, I highly recommend always doing such an action thus

Please Login or Register  to view this content.

Please Login or Register  to view this content.

_ If you do not do it like that then it can catch you out, as it did me.

_ Anyway, Sorry for the confusion.
_............................

So
The following 2 codes will work for you in a normal code module, or a sheet code module, as I am referencing all sheets explicitly: ( I also added an Error handler to make sure you always turn back on calculations even in the situation of a raised Error ).

Please Login or Register  to view this content.

_.....................

One last thing about your code:

Do you really want this at the end
.Calculation = xlCalculationAutomatic
.DisplayAlerts = False
Are you sure that should not be
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
_................................................

_-__--........._______________________________

......Just disable calculation, how on earth could have thought about that.
@Rorya, you are a gem. .......

It is always worth remembering that his suggestions, while often brief and un detailed , are almost always right!!!
I have a strong feeling he knows what he is doing, despite being mostly only a recent poster here....

Alan

[romperstomper]

while often brief and un detailed

Well, someone needs to balance you out...