By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,913 Members | 1,379 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,913 IT Pros & Developers. It's quick & easy.

Excel VBA: Runtime Error in Every Second Run

P: 1

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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim KeyCells As Range
  4.     Set KeyCells = Range("AnalysoitavatS[Note]")
  6. If Not Application.Intersect(KeyCells, Range(Target.Address)) _
  7.            Is Nothing Then
  9. Call kopioi_note
  11. End If
  12. End Sub
And here's the macro which does the copying :
Expand|Select|Wrap|Line Numbers
  1. ActiveCell.Offset(-1, 0).Copy
  2. ActiveCell.Offset(-1, -9).Select
  3.     Value2 = Selection
  4.      Sheets("KAIKKI HUOLLOT").Select
  5.       ActiveSheet.Unprotect Password:="1"
  6.     Range("MASTER[Order]").Select
  7.     Selection.Find(What:=Value2, After:=ActiveCell, LookIn:=xlFormulas, _
  8.         Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  9.         MatchCase:=False, SearchFormat:=False).Activate
  10.     ActiveCell.Offset(0, 21).Activate
  11.     ActiveSheet.Paste
  12.     ActiveSheet.Protect Password:="1"
  13.     Sheets("ANALYSOINTI").Select
  14.     Range("A3").Select
1 Week Ago #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,530
Hi ingengör.

Welcome to

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.
1 Week Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.