Error when executing VBA code to remove all formulas from Workbook

[brharrii]

I am developing a close procedure for some data entry forms. The procedure needs to do 3 things:

1 - Convert all formulas to call values

2 - Save the file in a predetermined location / file name

3 - Exit excel

I had successfully created code that would do numbers 2 and 3, but when I tried to add code to address number one I (shown in bold below) started getting a run time error:

Run-Time error '1004':

PasteSpecial method of range class failed

This is the code as i have it so far:

Please Login or Register  to view this content.

Does anyone have any idea what I might be doing wrong?

Thanks in advance

[AB33]

Please Login or Register  to view this content.

Where are you pasting the selection?

[Norie]

Try this for pasting values.

Please Login or Register  to view this content.

[brharrii]

@Norie, thank you very much, I'll play with this

@AB33 - I wanted to have it pasted over the back over itself so that all cells are the same as they were before but with values instead of formulas.

[AB33]

What I meant was you have not selected(Specified) in which cell you want to post the selected copy cells
In Norie's case, the selected cell is .Cells(1,1) -that where the selected copy will pasted in to.

[brharrii]

aaah, I see. Thank you AB33

[brharrii]

I seem to be struggling with this still.

I modified my code to add the cell reference and now I'm getting another error (I must have messed up the syntax?):

Please Login or Register  to view this content.

Compile error:

Invalid or unqualified reference

I also tried using Nories code snippet but I'm getting a similar error with that one:

Please Login or Register  to view this content.

Run-Time error '1004':
PasteSpecial method of Range class failed

[Norie]

Are there any protected sheets in the workbook?

Any sheets with charts/buttons/shapes?

[brharrii]

No sheets are protected, however there is a sheet with a text box that I'm using as a button to run the close procedure.

[Norie]

Do you want to copy/paste that worksheet?

[Cyclops]

Unqualified reference = leading dot not within with statement.

Please Login or Register  to view this content.

[brharrii]

It doesn't need to be. It would actually be better if it wasn't.

[Norie]

You can exclude that sheet, or any other, like this.

Please Login or Register  to view this content.

[brharrii]

Brilliant, Thank you all for your help

[Cyclops]

It doesn't need to be. It would actually be better if it wasn't.

I was just pointing out what was causing the compile error. It seems there were other issues as well but the unqualified reference was caused by the leading period with no reference object.

[Norie]

Cyclops

I think the OP was replying to my question about copying/pasting the worksheet with the button.

[brharrii]

You were right cyclops, I was responding to one of the other posters. Your comment helped me find one of the issues in the code, Thank you very much


I also gave "reputation" to everyone who helped me resolve this issue. Thank you all for your assistance