Macros

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 pa...

Macros and VBA

DKoontz

Iron Contributor

Jul 27, 2021

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.

Kathleen99

Copper Contributor

Jul 28, 2021

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

DKoontz
Iron Contributor
Jul 28, 2021
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.

Forum Discussion

Macros

Resources