Data Transer in another Sheets

[nidhipatel]

in sheet 1 Range A2 To A6 contins Student name lke

STUDENT NAME

Kalpesh Patel

Amit Shah

Vipul Rathod

Jigar Patel

Hitesh Doshi

<colgroup><col></colgroup><tbody>
</tbody>

i wish to this data add in sheet2 range A2. and every time data add in last blank row in sheet2. this is made without copy & paste method. this is made by for loop vba only.

 

[igold]

Errant Post

 

[igold]

Your question was a little ambiguous as it was not clear if you wanted all the student names in one cell or if you wanted the students names to be a on separate rows in column A. I have included codes to do both.

This code will run the student names down column A:

Sub MoveStudents()


    Dim stud As Variant
    Dim lRow As Long


    lRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    If lRow <= 2 Then lRow = 2
    stud = Worksheets("Sheet1").Range("A2:A6")
    Worksheets("Sheet2").Range("A" & lRow).Resize(UBound(stud)) = stud
    
End Sub

This code will put the student names in a single cell in column A, starting with Cell A2. I separated the names with a comma and space.

Sub MoveStudents2()


    Dim stud As Variant
    Dim lRow As Long, x As Long
    Dim studcell As String
    
    lRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
    If lRow < 2 Then lRow = 2
    stud = Worksheets("Sheet1").Range("A2:A6")
    For x = LBound(stud) To UBound(stud)
        studcell = studcell & ", " & stud(x, 1)
    Next
    studcell = Mid(studcell, 3)
    Worksheets("Sheet2").Range("A" & lRow) = studcell
    
End Sub