Forum Discussion

Kathleen99's avatar
Kathleen99
Copper Contributor
Jul 27, 2021

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 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?

Macros and VBA

3 Replies

  • DKoontz's avatar
    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's avatar
      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's avatar
        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.

Resources