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

Hello Andreas,

nope, the same error like before..

The same error, like my solution..

Answer 2

Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

Try the code below. If you get an error 1004 at

Set Ws = Sheets(Sheets.Count)

add the line

DoEvents

before that line.

Andreas.

Sub Test()
  Dim Ws As Worksheet
  Sheets("Muster").Copy After:=Sheets(Sheets.Count)
  Set Ws = Sheets(Sheets.Count)
  With Ws.UsedRange
    .Value = .Value
  End With
End Sub

Answer 3

Here is more Information:

...

Sheets("Muster").Select

    Sheets("Muster").Copy After:=Sheets(Sheets.Count)

' This convert Formula to text

    With ActiveSheet.UsedRange

        .Value = .Value

    End With

    Sheets("Muster (2)").Select

...

Answer 4

I'm sorry, your code makes no sense to me.

It does not appear to do anything.

You are setting the value equal to itself.

Answer 5

Can you show us few more lines preceding this code so that we understand the code better?