SOLVED

cannot paste special with macro

Occasional Contributor

im trying to do something like this

jaypee1650_1-1626018687228.png

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

4 Replies

@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
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
best response confirmed by jaypee1650 (Occasional Contributor)
Solution

@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

 

thats what i need thanks