Excel VBA - Repeating Cell Range in different sheet

Question

I am trying to copy a range of cells that contain names, such as:

UBBR1
UBBR2
UESR1
UESR2
UDCR1
UDCR2
SBBR1
SBBR2
SESR1
SPDCR1
SPDCR2
SADCR1
GCCS-M DBM
SADCR2
SPDCR3
SCIBBR1
SCIDCR1
SVTCR1

The range of those cells is stored in a variant called mapping listed below.Into another sheet. The number of times these names get repeated depends on the number of rows that are in another sheet. Each name above has a unit number that is in its same row, I store in another variant called ddg. The unit numbers stored in ddg refer to the names of different sheets in this workbook where the tables of data I am using are stored. For example, 2 below refers to "Unit #2" which is a table that has 38 rows, so UBBR1(Above) would need to be repeated 38 times.

ddg:

Code:

ddg = ws.Range("E4:E21").Value
mapping = ws.Range("B4:B21").Value

For Each k In ddg
        m = "Unit #" & k
        lastN = Sheets("Test").Range("B50000").End(xlUp).Row + 1

    For Each i In mapping


       N = Cells(Sheets(m).Rows.count, 1).End(xlUp).Row

        For j = 1 To N


        Sheets("Test").Range("B" & lastN).Value = i

        Next j


    Next i

Next k

Where i is a variant and mapping is also a variant that contains the range of the cells that contain the names. N is a long that gets row count of the sheet for the repetition. I am not sure what I am doing wrong but the loop does not repeat the values instead it loops through everything in mapping, leaves that in the cell and does the same all the way down. Any help would be greatly appreciated.

You need to show the rest of your code, particularly where you assign mapping — Tim Williams
You are overriding the value in "B" & N j times, always with the same value of i. Can you provide expected results based on your given sample data? — tigeravatar
Why is Greg repeated 4 times but Julie only twice, and 5 Joe's ? — Tim Williams
The number of times they are represented depends on the number of rows in the sheets those names correspond to. For example, Greg, represents a sheet that has 4 rows so it gets repeated 4 times. — kdean693

Tim Williams Tim Williams · Accepted Answer · 2017-06-20T20:36:13

Untested, but something like this should work

Sub Tester()

    Dim ddg, mapping, k As Long,  N As Long
    Dim unitNum, nm

    '...
    '...

    ddg = ws.Range("E4:E21").Value
    mapping = ws.Range("B4:B21").Value

    For k = 1 To UBound(ddg, 1)

        unitNum = ddg(k, 1)
        nm = mapping(k, 1)

        With Sheets("Unit #" & unitNum)
            N = .Cells(.Rows.Count, 1).End(xlUp).Row
        End With

        Debug.Print N & " rows for Unit# " & unitNum & " (" & nm & ")" '<<<

        Sheets("Test").Range("B50000").End(xlUp).Offset(1, 0) _
                                      .Resize(N, 1).Value = nm

    Next k


End Sub

You only need one loop, and you can write all the values in one shot.

Excel VBA - Repeating Cell Range in different sheet

1 Answers