VBA Copy data based on count resultClosed

Question

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.

TrowaD · Answer

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

VBA Copy data based on count result

1 response

Similar discussions

Subscribe To Our Newsletter!