Converting my macro record to a vba code (continuous)

[papare]

Hello

I am trying to automate inserting of data. i have an insert button which is to copy the entries into relevant columns (see record macro below). the limitation with the macro below is when i insert new data entries it wipes out the previous one.

Sub insertdata2()'
' insertdata2 Macro
'


'
Range("C2,C4,C6,C8,C10").Select
Range("C10").Activate
Selection.Copy
Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("A3").Select
Sheets("Sheet1").Select
Range("D8").Select
Application.CutCopyMode = False
Range("C2:C10").Select
Selection.ClearContents
Range("C2").Select
End Sub

Explanation:

Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("A3").Select

After coping data from Sheet1, its to paste on Data, first selected cell (A2)
for continuity i was hoping next insert will be pasted on active cell A3 and then A4 etc.

Kindly assist.
Papare

 

[jsotola]

here are some examples of copy routines

it is best to avoid the

     sheet.select
     firstRange.select
     selection.copy
     anotherSheet.select
     anotherRange.select
     selection.paste

and just name the source and destination directly

     sheet.firstRange.copy
     anotherSheet.anotherRange.paste

this way the code works no matter which worksheet is selected

Sub insertdata2()'
' resize this window and workbook window so that you can see both
'
' click anywhere in this Sub and hit F8 to step through code and watch the result on workbook


'
' insertdata2 Macro


    ' this one copies alternating cells
    Sheets("Sheet1").Range("C2,C4,C6,C8,C10").Copy
    Sheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                            SkipBlanks:=False, Transpose:=True
    
    ' this one copies contiguous block
    Sheets("Sheet1").Range("C2:C10").Copy
    Sheets("Data").Range("A3").PasteSpecial Paste:=xlPasteValues, _
                                            Operation:=xlNone, _
                                            SkipBlanks:=False, _
                                            Transpose:=True
    
    ' both of the above transpose from a vertical range to a horizontal range on the destination sheet
    
    ' this one copies cells from one place to another without transposition
    Sheets("Sheet1").Range("C2:C10").Copy Destination:=Sheets("Data").Range("A4")
    
    ' this one copies into multiple columns
    Sheets("Sheet1").Range("C2:C10").Copy Destination:=Sheets("Data").Range("A4:D4")
    
    Application.CutCopyMode = False                 ' deselects all selected cells
    
    Sheets("Sheet1").Range("C2:C10").ClearContents  ' clears the original data on Sheet1 
    
End Sub

 

[AlphaFrog]

Do you want to paste to the next empty row in column A on sheet Data? If yes, the try something like this.

[color=darkblue]Sub[/color] insertdata2()
    
    Sheets("Sheet1").Range("C2,C4,C6,C8,C10").Copy
    Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
        Paste:=xlPasteValues, Transpose:=[color=darkblue]True[/color]
    Sheets("Sheet1").Range("C2:C10").ClearContents
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[papare]

Thanks, my main problem is continuity. This routine stops and deletes previous entries.
I would like for the next entry to go the below cell e.g. A3, A4, A5, A6, A7 etc.

 

[papare]

Thanks works