Problems with VBA Code

Question

Hello,

i use for copy & paste a worksheet the following Code:

 With ActiveSheet.UsedRange

        .Value = .Value

    End With

Sometime ist works, sometime i get an error "1004" on the line .Value = .Value.

It is the same file.

I copy the complete file under a  new name, but i got the same errors, sometime, sometime not.

Used with different Version (2013/2016) on different OS-Systems (Win7/Win10) and different HW (PC/TerminalClient)

Can anyone help me?

THX

Michael

Answer 1

I knew it. In 90% of all cases are merged cells the reason why a code fails. :-)

The solution is to search for all formulas in the sheet, then check each location if there is a merged cell, if so use the MergeArea property and convert the formula using .Value = .Value

Do you need help to write the code for that?

Andreas.

Answer 2

Hello,

i think i found the problem..

In my worksheet are merged cells.

These are the problem.

If i cut the cells, then it works without problems

With merged cells, most time not, sometime yes.

Anx ideas?

THX

michael

Answer 3

Then the error is due to the layout of the sheet... merged cells?

Also an AddIn can make problems. Please follow these steps:

Close Excel
Press and hold the CTRL key
Open Excel
Wait for a message to appear and ask for "Safe Mode"
Release the CTRL key
Click Yes
After Excel opens
Click File\Open and select your file
Press and hold the SHIFT key
Click Open
Wait for your file to open
Release the SHIFT key
Test the behavior

Andreas.