duplicating range

hey guys. i need your help
i'm trying to save time by VBAing simple process,
I need to duplicate a specific Range as many time as another range have + i need to inseret additional field to the duplicated range.
i explain better:
i have a range of products and a range of shops numbers.
i want to duplicate the range of products as many times as the number of shops i have, but also for each paste of range i want it to add additional column with the shop number. so at the end i will have for example: if i have 6 shops, 1 till 6. than i will have the products range X6 times but will have 6 diffrent shops number.

1	A
1	B
1	C
2	A
2	B
2	C
3	A
3	B
3	C
4	A
4	B
4	C
5	A
5	B
5	C
6	A
6	B
6	C

thanks for all your help!!
Raz

This is easy enough to do, but we need more detail:
1:where is the range to be duplicated specified, presumably the range of product
2: Where is the list of shps specified
3: where do you want the result
4: Do you want shops in the columns and porducts in the rows or the other way round, or do you want them in one long list, if so what order

Quote:

---

Originally Posted by offthelip

This is easy enough to do, but we need more detail:
1:where is the range to be duplicated specified, presumably the range of product
2: Where is the list of shps specified
3: where do you want the result
4: Do you want shops in the columns and porducts in the rows or the other way round, or do you want them in one long list, if so what order

---

hey.
i want to duplicate the range of products
i have a sheet of products list, another sheet of shops and i want to duplicate it all to new sheet.
i want it all the same. first column is the shops for example and the following is the products (like the example)
so that in the new sheet it will be shown like:

shop	products	product color
1	A	Black
1	B	black
1	C	white
2	A	Black
2	B	black
2	C	white
3	A	Black
3	B	black
3	C	white
4	A	Black
4	B	black
4	C	white
5	A	Black
5	B	black
5	C	white
6	A	Black
6	B	black
6	C	white

You'll need to at least adjust the ranges

Code:

---

Option Explicit

Sub MixingUp()

    Dim rShops As Range, rProducts As Range
    Dim rShop As Range, rProduct As Range
    Dim wsOutput As Worksheet
    Dim iOut As Long
   
    Set rShops = Worksheets("Shops").Cells(1, 1)
    Set rShops = Range(rShops, rShops.End(xlDown))
   
    Set rProducts = Worksheets("Products").Cells(1, 1)
    Set rProducts = Range(rProducts, rProducts.End(xlDown))
    Set wsOutput = Worksheets("Shops+Products")
    iOut = 2
   
    Application.ScreenUpdating = False
   
    For Each rShop In rShops.Cells
        For Each rProduct In rProducts.Cells
            wsOutput.Cells(iOut, 1).Value = rShop.Value
            wsOutput.Cells(iOut, 2).Value = rProduct.Value
            wsOutput.Cells(iOut, 3).Value = rProduct.Offset(0, 1).Value
            iOut = iOut + 1
        Next
    Next
    Application.ScreenUpdating = True
End Sub

---

Arrays might be a little faster, but this is simpler I think

Amazing!! thanks!!!