-3

I'm new to vba.

I have a list of data (A2:A19) on sheet1 and I need to paste it on sheet2 but for each data I need to skip 7 rows. I tried the code for the paste but failed. I dont know how to do the looping(i think). tq

enter image description here

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
young bella
  • 1
  • 1
  • 2
    Your own effort is lacking both in the sense that you don't publish any code you wrote and which might be corrected and that you don't publish a description of the result according to which code might be written. If you can't improve the quality of your question, preferably in both regards, this thread will be closed within the next half hour - at the most. – Variatus Oct 30 '20 at 02:42

2 Answers2

0

Here's some code that would do it.


Sub InsertRow()

Dim Src As Range
Dim Dest As Range
Dim x As Long
Dim y As Long
Dim cel As Range

Set Src = ThisWorkbook.Worksheets("Sheet1").Range("a2:a19")
Set Dest = ThisWorkbook.Worksheets("Sheet2").Cells(y, 1)
x = 1
y = ThisWorkbook.Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Row

For Each cel In Src
    Dest = cel
    x = x + 8
    Set Dest = ThisWorkbook.Worksheets("Sheet2").Cells(x, 1)
Next

End Sub
Hooded 0ne
  • 881
  • 1
  • 3
  • 10
  • hi sir, thank you for your answer. I modified a bit, If I want to paste new list so that it ca paste after the last row. however it didnt work.could you please correct me? is there something wrong in my code? Set Dest = ThisWorkbook.Worksheets("June").Range("A" & Rows.Count).End(xlUp) – young bella Oct 30 '20 at 03:37
  • I've edited my post. Range is weird (see post below about unqualified ranges).https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells – Hooded 0ne Oct 30 '20 at 03:49
  • I've tried your code also didnt worked out. But I also tried to a new coding. but it only copy and paste the last cells. the code are in the answer section as its too long to post in comment. could you please take a look at my coding. what went wrong? – young bella Nov 04 '20 at 02:46
0

Sub copypasteskip()

Dim name As String Dim src As Variant Dim dest As Variant Dim endnumber As Integer Dim finalrow As Variant Dim i As Integer Dim r As Integer

Set src = ThisWorkbook.Worksheets("ImportData") Set dest = ThisWorkbook.Worksheets("June")

endnumber = Cells(Rows.Count, "A").End(xlUp).Row finalrow = Worksheets("ImportData").Cells(Rows.Count, "D").End(xlUp).Row

'MsgBox "ok"

For r = 11 To endnumber

    'name = Cells(r, "A").Value
    
    For i = 14 To finalrow
    
    Worksheets("ImportData").Cells(i, "D").Copy
    Worksheets("June").Cells(r, "A").PasteSpecial xlPasteAll
    
    
    Next i
    
    
    r = r + 7

Next r

End Sub

young bella
  • 1
  • 1
  • Your code isn't even formatted correctly, so I'm not going to review it. I would suggest creating a new question if you now have additional requirements and post this code as what you have attempted so far. – Hooded 0ne Nov 05 '20 at 17:05
  • noted sir. i just know that need to put three ``` if want to post the code. – young bella Nov 06 '20 at 03:32