How to copy data from sheet1 to sheet2

Category: [Excel] Demo Available

How to copy data from sheet1 to sheet2

Requirement:

The user wants to copy data from sheet1 to sheet2 ---> e.g. first row in sheet1 A1:C1 to first three visitor as shown in sheet 2 (A2 to C2).
Then copy other row from sheet1 to sheet2 accordingly (as shown in below sheets)

sheet1

	A	B	C
1	11	22	33
2	14	25	36
3	17	28	39
4	20	31	42
5	23	34	45
6	26	37	48
7	29	40	51
8	32	43	54
9	35	46	57

sheet2

visitor 1

visitor 2

visitor 3

visitor 4

visitor 5

visitor 6

visitor 7

visitor 8

visitor 9

visitor 10

visitor 11

visitor 12

My VBA code is as below, of course it doesn't work. It will keep loop "next k" until k=9

but I want to the code run i=1, then k=1, then i=2, k=2, then so on....

Code:

Sub copypaste()


For i = 1 To 9
    
sheet1.Range(Cells(i, 1), Cells(i, 3)).Copy
    
    For k = 20 To 47 Step 3
    sheet2.Cells(2, k).PasteSpecial Paste:=xlPasteValues


    Next k


Next i

End sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149318-vba-question-about-loop-of-data-copy-and-paste-using-for-to-next

Solution:

Code:

Sub copypaste()
Dim i As Long
Dim k As Long
k = 20
    For i = 1 To 9
        Sheet1.Range("A" & i & ":C" & i).Copy
        Sheet2.Cells(2, k).PasteSpecial Paste:=xlPasteValues
        k = k + 3
    Next i
Application.CutCopyMode = xlCut
Application.GoTo Sheet1.Range("A1")
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Carim.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.

	A	B	C
1	11	22	33
2	14	25	36
3	17	28	39
4	20	31	42
5	23	34	45
6	26	37	48
7	29	40	51
8	32	43	54
9	35	46	57

	A	B	C
1	11	22	33
2	14	25	36
3	17	28	39
4	20	31	42
5	23	34	45
6	26	37	48
7	29	40	51
8	32	43	54
9	35	46	57

	A	B	C
1	11	22	33
2	14	25	36
3	17	28	39
4	20	31	42
5	23	34	45
6	26	37	48
7	29	40	51
8	32	43	54
9	35	46	57