cannot paste special with macro

im trying to do something like this

selected cells will insert the data in the above table

a video is attached to show what i mean

i dont know how to type macros so i recorded the actions i want

i used the relative references button too to do that

this is the code

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A14:B14").Select
    Selection.Copy
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    Range("A14:B14").Select
    Selection.ClearContents
End Sub

everything works so far but what i want is pasting only values not the formatting too if i try that i get this error thing: runtime error 1004 pastespecial method of range class failed

if you need more stuff to help me just tell me and thanks in advance

Perhaps

Sub Macro2()
    Worksheets("TargetSheet").Range("A1").End(xlDown).Offset(1, 0).Resize(1, 2).Value = Worksheets("MainSheet").Range("A14:B14").Value
    Worksheets("MainSheet").Range("A14:B14").ClearContents
End Sub

4 Replies

HansVogelaar
MVP
Jul 11, 2021
jaypee1650

Try this:

Sub Macro2() Range("A1").End(xlDown).Offset(1, 0).Resize(1, 2).Value = Range("A14:B14").Value Range("A14:B14").ClearContents End Sub
- jaypee1650
  Copper Contributor
  Jul 11, 2021
  this works in this example but i need to do it with multiple sheets each sheet contains specific data and one main sheet which i will insert the data from
  - HansVogelaar
    MVP
    Jul 11, 2021
    jaypee1650
    
    Perhaps
    
    Sub Macro2() Worksheets("TargetSheet").Range("A1").End(xlDown).Offset(1, 0).Resize(1, 2).Value = Worksheets("MainSheet").Range("A14:B14").Value Worksheets("MainSheet").Range("A14:B14").ClearContents End Sub

Forum Discussion

cannot paste special with macro

4 Replies

Resources