Forum Discussion

Locky81's avatar
Locky81
Copper Contributor
Aug 22, 2022
Solved

Result from macro copy and paste error

Hi Team,

 

I have a value in a cell that I'm trying to simply copy and paste (as value only), that I've programmed into a simple macro. When this value is the product of a formula the macro driven copy and paste will not work, but if i remove said formula and directly type the value in the macro driven copy and paste does the job!! Can anyone suggest what I'm doing wrong?

Cheers, locky.

Excel for web
office 365
  • Harun24HR's avatar
    Harun24HR
    Aug 22, 2022

    Locky81 Understood your problem. Actually you are pasting twice. So you do not need second ActiveSheet.Paste line. Delete it from your code. I have comment it in my below codes.

     

    Sub EXECUTECOPYPASTE()
    Range("K5").Select
    Selection.Copy
    Range("L15").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

     

      Basically you can do it by below single line code.

    Sub CopyValue()
    Range("L15").Value = Range("K5").Value2
End Sub
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Aug 22, 2022

    Locky81 Understood your problem. Actually you are pasting twice. So you do not need second ActiveSheet.Paste line. Delete it from your code. I have comment it in my below codes.

     

    Sub EXECUTECOPYPASTE()
    Range("K5").Select
    Selection.Copy
    Range("L15").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        'ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

     

      Basically you can do it by below single line code.

    Sub CopyValue()
    Range("L15").Value = Range("K5").Value2
End Sub
    • Locky81's avatar
      Locky81
      Copper Contributor
      Aug 22, 2022
      Thanks so much mate, it's working for me now. Kudos!
      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor
        Aug 22, 2022
        If it works then please tick mark the answer.
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Aug 22, 2022
    Post you current code here so that we can review it. When this value is the product of a formula the macro driven, then you do not need copy and paste. You can put that value to a cell directly via code.
    • Locky81's avatar
      Locky81
      Copper Contributor
      Aug 22, 2022
      That's my created Macro - just a copy and paste to a different cell. The cell populates with my number (value) only if another cell is Triggered to display TRUE. If TRUE my copy-target cell for the Macro changes from a zero to a number.
    • Locky81's avatar
      Locky81
      Copper Contributor
      Aug 22, 2022

      Harun24HR 

      Sub EXECUTECOPYPASTE()
      '
      ' EXECUTECOPYPASTE Macro
      '

      '
      Range("K5").Select
      Selection.Copy
      Range("L15").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      ActiveSheet.Paste
      Application.CutCopyMode = False
      End Sub

      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor
        Aug 22, 2022
        The codes looks pretty good. What problem you are facing with this code?

Resources