PDA

View Full Version : [SOLVED] duplicating range



razlevav
01-25-2017, 06:20 AM
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

offthelip
01-25-2017, 06:49 AM
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

razlevav
01-25-2017, 06:59 AM
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

Paul_Hossler
01-25-2017, 07:25 AM
You'll need to at least adjust the ranges




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

razlevav
01-25-2017, 07:50 AM
Amazing!! thanks!!!