# Macro to duplicate rows based on cell value in another worksheet

#### Flipmodes

##### New Member
Sorry - i've been looking all morning and can't seem to find what I'm looking for. I stick with code also, so hopefully some of you smart folks can help...

Here's the need (I figured a graphic would do more justice than me trying to explain):

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### MickG

##### MrExcel MVP
Try this:-
Results start "D1"
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG15Apr25
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 1
[COLOR="Navy"]Set[/COLOR] Rng1 = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng2 = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
Range("D1").Resize(Rng1.Count) = Rng1.Value
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng2
[COLOR="Navy"]With[/COLOR] Range("E" & c)
.Resize(Rng1.Count) = Dn
.Offset(, 1).Resize(Rng1.Count) = Rng1.Value
[COLOR="Navy"]End[/COLOR] With
c = c + Rng1.Count
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

#### dave3009

##### Well-known Member
Something else to try

Code:
``````Sub create_list()
Dim lstV, lstC As Long
Dim i, j As Long
Dim currRow As Long

With Sheet1
lstV = .Range("A" & Rows.Count).End(xlUp).Row
lstC = .Range("B" & Rows.Count).End(xlUp).Row
End With

currRow = 2

For i = 2 To lstC
For j = 2 To lstV
Sheet2.Range("A" & currRow) = Sheet1.Range("B" & i)
Sheet2.Range("B" & currRow) = Sheet1.Range("A" & j)

currRow = currRow + 1
Next j
Next i

End Sub``````

#### Flipmodes

##### New Member
MickG - your solution works perfectly thanks!
Dave - I tried yours also, no errors but nothing happens.

Thanks both for the quick replies.

Replies
0
Views
229
Replies
10
Views
604
Replies
1
Views
95
Replies
4
Views
258
Replies
1
Views
194

1,195,669
Messages
6,011,061
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back