How To copy data from a range in sheet1 to sheet2 5 times until I reach an empty cell?

Question

I am working on a excel project and I am struggling at the moment with the following.

I am trying to copy data from A2:C2 from sheet1 to sheet2 until I reach an empty row in sheet1. Also I need to be able to copy each line of data into sheet2 five times. So copy A2:C2 from sgheet1 to sheet2 and paste it in sheet2 five times. Continue until I reach an empty row in sheet1. Many thanks for any help or assistance.

Here is the code so far:When I run step by step it copies the first data into sheet 2 five times perfect but then instead of moving onto the next row in sheet 1 it continues to copy the first data into sheet 2

Sub Macro1()
'
' Macro1 Macro
'copy normal data

''Loop until a blank cell is found in Column b



  Sheets("Sheet1").Select
    Range("B2:D2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B2:B6").Select
    ActiveSheet.Paste
    Columns("B:B").EntireColumn.AutoFit
    Sheets("Sheet1").Select
    Range("B2:D2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Sheets("Sheet2").Range("b" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial



Do While BlankFound = False
  x = x + 1
  If Cells(x, "b").Value = "" Then
      BlankFound = True
    End If


  Loop

Please [edit] your question and add the code you have tried so far and ask a question to that code. Eg. where did you get stuck or where did you get errors and which. Reading [ask] and [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) and [No attempt was made](http://idownvotedbecau.se/noattempt/) might help to improve your question. — Pᴇʜ, Apr 12 '18 at 08:17
I recommend to read and follow [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Apr 12 '18 at 09:47

Michał Turczyn · Accepted Answer · 2018-04-12T09:59:43.620

0

Try this:

Option Explicit
Sub CopyRows()
    'always declare all variables
    Dim i As Long, lastRow As Long, ws1 As Worksheet, ws2 As Worksheet, k As Long
    'set references to worksheets, as we will use them in this sub
    Set ws1 = Sheets("sheet1")
    Set ws2 = Sheets("sheet2")
    k = 1
    'determine last non-blank cell in B column in sheet1
    lastRow = ws1.Cells(ws1.Rows.Count, 2).End(xlUp).Row
    For i = 2 To lastRow
        'loop until last row in B column and copy five times A-C cells to A-C columns in sheet2
        ws2.Range("A" & k & ":C" & (k + 4)).Value = ws1.Range("A" & i & ":C" & i).Value
        k = k + 5
    Next

End Sub

edited Apr 12 '18 at 09:59

answered Apr 12 '18 at 09:26

Michał Turczyn

32,028
14
47
69

Perfect! Thanks Michal. I have tried to edit so that it would start to copy at A2 rather than A1 but it fails for me. I want to Leave A1 as a header. – John Mc Apr 12 '18 at 09:50
1

@JohnMc Well just start at `i = 2` in the `For` loop. – Pᴇʜ Apr 12 '18 at 09:56
Perfect Michael - this is a great help – John Mc Apr 12 '18 at 10:12
@ Michał Turczyn Thank you – John Mc Apr 12 '18 at 12:52
@Michał Turczyn works a treat i changed the value of k to 2 and it copies into the second row in sheet2. Thanks again – John Mc Apr 13 '18 at 12:58
@JohnMc I am glad I could help. – Michał Turczyn Apr 13 '18 at 20:40
@Michał Turczyn do you think Michael it could be possible not to copy the data 5 times but to copy it based on values in column E,F,G,H and I. What I mean is that if there were values only in column E&F then copy the data 2 times rather than 5. Is it possible to look to see if there is data in E,F,G,H and I and based on the number of data inputs that would be the amount of times to copy? The data inputs are just numbers,text. – John Mc Apr 14 '18 at 11:50

How To copy data from a range in sheet1 to sheet2 5 times until I reach an empty cell?

1 Answers1