Macros

%3CLINGO-SUB%20id%3D%22lingo-sub-2592929%22%20slang%3D%22en-US%22%3EMacros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2592929%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%203%20macros%20with%20basic%20copy%20and%20paste%20functions%20within%20them.%26nbsp%3B%20They%20work%20perfectly%20the%20first%20time%20only.%26nbsp%3B%20Then%20I%20get%20%22runtime%20error%201004%22%20and%2For%20random%20results%2C%20such%20as%20moving%20to%20another%20part%20of%20the%20worksheet%20or%20pasting%20partially%2C%20in%20wrong%20cells.%3C%2FP%3E%3CP%3EI%20have%20adjusted%20my%20security%20settings%20to%20%22trust%20access%20the%20VBA%20project%20object%20model%22%26nbsp%3B%20No%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2592929%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2593279%22%20slang%3D%22en-US%22%3ERe%3A%20Macros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2593279%22%20slang%3D%22en-US%22%3ECan%20you%20post%20your%20code%3F%20It%20could%20be%20due%20to%20using%20active.%20references%20or%20something%20that%20throws%20it%20off%20the%20second%20time%20you%20run%20it.%20I've%20run%20into%20similar%20issues%20and%20it%20usually%20fixes%20itself%20once%20I%20explicitly%20name%20the%20workbooks%20and%20worksheets.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have created 3 macros with basic copy and paste functions within them.  They work perfectly the first time only.  Then I get "runtime error 1004" and/or random results, such as moving to another part of the worksheet or pasting partially, in wrong cells.

I have adjusted my security settings to "trust access the VBA project object model"  No change.

 

Any ideas?

3 Replies
Can you post your code? It could be due to using active. references or something that throws it off the second time you run it. I've run into similar issues and it usually fixes itself once I explicitly name the workbooks and worksheets.
I rarely use Macros so reading this code is painful for me...
Sub Week1()
'
' Week1 Macro
'
' Keyboard Shortcut: Ctrl+b
'
ActiveCell.Offset(2, -5).Range("A1:C1").Select
Selection.Copy
ActiveCell.Offset(-2, 5).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=30
ActiveCell.Offset(36, -3).Range("A1:A24").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Sub Week3()
'
' Week3 Macro
'
' Keyboard Shortcut: Ctrl+c
'
ActiveCell.Offset(-34, -2).Range("A1:C1").Select
Selection.Copy
ActiveCell.Offset(-2, 5).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=33
ActiveCell.Offset(36, -3).Range("A1:A24").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(16, 0).Range("A1").Select
ActiveWindow.SmallScroll Down:=-150
End Sub
Sub Week4()
'
' Week4 Macro
'
' Keyboard Shortcut: Ctrl+d
'
ActiveCell.Offset(8, -12).Range("A1:C1").Select
Selection.Copy
ActiveCell.Offset(-2, 5).Range("A1:C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=36
ActiveCell.Offset(36, -3).Range("A1:A24").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

 

@Kathleen99 

Sheets("Sheet1").Range("A1:C1").Copy Range("H1")

The active cell references is probably what's messing this up on your end. If you are looking to just copy and paste using VBA, try the above code. If you change sheet1 to match which sheet you're trying to copy and paste on, change the first "A1:C1" range to whatever you want to copy, and your output cell, "H1" will be where it is pasted. This may work for what you need.

 

The record macro feature records a lot of unnecessary steps. Let me know if this works.