Excel VBA: Runtime Error in Every Second Run

[ingengör]

Hi,

I have a problem. I have created macro which copies text from one cell and paste it to other cell in other sheet.This macro is called from other macro so that if you change any cell in specific range then it will call this macro.

Now it works every second time but when does not run time error 1004 occurs. it seems that error occurs during activesheet.paste command. What should i do?

Here's the mmacro which calls other macro when specific cells are changed :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
 
    Set KeyCells = Range("AnalysoitavatS[Note]")
 
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
 
Call kopioi_note
 
End If
End Sub

And here's the macro which does the copying :

ActiveCell.Offset(-1, 0).Copy
ActiveCell.Offset(-1, -9).Select
    Value2 = Selection
     Sheets("KAIKKI HUOLLOT").Select
      ActiveSheet.Unprotect Password:="1"
    Range("MASTER[Order]").Select
    Selection.Find(What:=Value2, After:=ActiveCell, LookIn:=xlFormulas, _
        Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 21).Activate
    ActiveSheet.Paste
    ActiveSheet.Protect Password:="1"
    Sheets("ANALYSOINTI").Select
    Range("A3").Select

[NeoPa]

Hi ingengör.

Welcome to Bytes.com.

ingengör:
And here's the macro which does the copying :

Unfortunately that's only part of the procedure as it doesn't include the definition - either of the procedure itself or any of the variables you're using. A bit like working with a blindfold on. It would also be helpful to share the error message rather than just the number.

Otherwise this question is well asked and as a first time is pretty decent. Well done.

I'll add comments as they occur to me. If I cannot necessarily solve the problem I can at least raise issues for you to think about.

I'll start by saying that this is clearly an Excel question rather than Access. This is fine, but people need to understand that so I'll update the title to make that clear. This is, after all, an Access forum.

From your Worksheet_Change() event procedure I noticed you used Range(Target.Address). That's saying :
Take a Range, convert it to the matching address, then convert it back into a Range again. IE. It leaves you where you started with Target. A bit strange but unlikely to be the cause of any problems.

Congratulations on using .Find() in such a clear way. Named arguments make it so much easier to follow and harder to get wrong. Also using the line continuation character (_) makes it so much easier to read & follow.

As far as what's causing the problems on alternate runs goes I can't see anything obvious. It doesn't help that I have very little idea of what's in your Workbook of course, but I can suggest you check that where the Selection of Cells and Worksheets are after the code finishes running the first time is not causing the issues when you run it the next time. Another thing to check is whether or not there's anything on the Clipboard. I see no code to Cut or Copy, nor do I see anything to clear the Clipboard after the .Paste (Application.CutCopyMode = False).

Let us know if any of this helps.