VBA Copy data based on count result

Closed
Report
Posts
11
Registration date
Sunday April 8, 2018
Status
Member
Last seen
April 19, 2018
-
Posts
11
Registration date
Sunday April 8, 2018
Status
Member
Last seen
April 19, 2018
-
Hi, I am trying to build in the following code in order to count how many cells in a row have data. Based on the count I then need to Copy cells by the same number as the count. For instance if the count was 3 then I need to copy the data 3 times.

n = Worksheets("Sheet1").Range("C2:P2").Cells.SpecialCells(xlCellTypeConstants).Count

The data to be copied three times would be located in Sheet1 A2:B2 and needs to be copied to Sheet2 A2:B2
I would need to continue this until I reach an empty row in sheet1
Many thanks for any help or assistance.

1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi John,

So if I understand you correctly then, you want to loop through your sheet until n=0.

Give the following code a shot:
Sub RunMe()
Dim lRow, n, x As Integer

On Error Resume Next

Sheets("Sheet1").Select
Range("A2:B2").Copy

x = 2
lRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
n = Range(Cells(x, "C"), Cells(x, "P")).Cells.SpecialCells(xlCellTypeConstants).Count

Do
    Do
        Sheets("Sheet2").Range("A" & lRow).PasteSpecial
        lRow = lRow + 1
        n = n - 1
    Loop Until n = 0
    x = x + 1
    n = Range(Cells(x, "C"), Cells(x, "P")).Cells.SpecialCells(xlCellTypeConstants).Count
Loop Until n = 0

Application.CutCopyMode = False

End Sub


When a row within the columns C:P is empty, Excel produces an error. Because of that I used the line: On Error Resume Next. This isn't advisable, but in this case (simple code) it can't hurt.

Best regards,
Trowa

0
Posts
11
Registration date
Sunday April 8, 2018
Status
Member
Last seen
April 19, 2018

thank you I will give it a shot..
0