Repeat row x timesClosed

Question

Hello, 
I want to make some labels for products. i have a sheet with info in columns A-F(name, sku, size, description, date, and quantity. 
Column F contains quantities. I need to make a sheet that repeats each row x times with x = the number in F column. Below is an example of a row. Keep in mind i have about 1000 rows like this.
ALIVIA |	1148  |	S  |	BLOUSE  |	1/1/2016  |	4 
Thank you so much for your time and effort,

vcoolio · Answer

Hello Richard,
Perhaps the following code, assigned to a button, may do the task for you:-
Sub CopyRows()

Dim r As Range

For Each r In Range("F2", Range("F" & Rows.Count).End(xlUp))
   Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(r.Value, 5) = r.Offset(, -5).Resize(1, 5).Value
     Next r
     
End Sub
I've prepared a little sample at the following link for you to play with:-
https://www.dropbox.com/s/edpbkfor4j9vlay/Copy%20x%20number%20of%20rows%20to%20sht%202.xlsm?dl=0
Click on the "RUN" button to see it work.
I hope that this helps.
Cheerio,
vcoolio.

Richard Pavelka · Answer

Thank you for trying to help me.
 I can not get the sample you sent to work. I press run and nothing happens. Please let me know if I am doing something wrong.
Thank you,

vcoolio · Answer

Hello Richard,
Did you check Sheet 2? The result is in Sheet 2. It happens very quickly so you probably weren't aware.  
Let us know.
Cheerio,
vcoolio.

Richard Pavelka · Answer

Thank you for trying to help.
I have tried several times and this example does not work. There are titles on sheet 2 but no info.
I am not sure how to use this code either.
Again, thank you

vcoolio · Answer

Hello Richard,
Everything works just fine on my end. DropBox is just a file sharing site like many others so that's not the problem. It could be the trust settings on your machine.
Just wondering, when you download the file, a preview page will appear first. If you try to click on the button here, nothing will happen. You need to click on "Download" (top right) and then open the file from the file download bar that appears. When you have opened the actual file, a gold coloured bar will appear towards the top of the page titled "Protected View". Click on the "Enable Editing" button. Another gold coloured bar will then appear titled "Security Warning". Click on the "Enable Content" button. You should then be right to go.
If this still does not work from your end, then upload a sample of your work book ( a dozen lines or so will do) to a free file sharing site such as DropBox, ge.tt or Sendspace and then post the link to your file back here. If your data is sensitive, then please use dummy data. I'll then implement the code within your sample.
Cheerio,
vcoolio.

Richard Pavelka · Answer

This works perfectly. How would I modify it to include more columns if needed?
Also, how do I put this into my worksheet?
Thank you so much.

vcoolio · Answer

Hello Richard,
Excellent!
If, in future, you need to add more columns, lines 5, 6 and 7 in the code above will need some minor alteration.
The "Quantity" column (F) needs to remain as the last column of data so change this in line 5 to whichever column will be the new "Quantity" column (it may be K or N or T......).
You will then need to change the number 5 in lines 6 and 7 to reflect the new amount of columns that you are using. You presently have six columns of data but the "Quantity" column does not need to be transferred to Sheet 2 so we only need to deal with Columns A:E (5 columns). Bear this in mind should you ever add more columns.
You can always come back here for help in future should you need to make any changes.
To implement the code:-
- Press Alt + F11. This will open the VB Editor.
- From the ribbon at the top of the page, select "Insert" and then "Module".
- The large grey field will turn white.
- Paste the above code in this white field.
- Go back to your work sheet.
This is how I usually create a button:-
In the work sheet, from the ribbon at the top, select "Insert". From the "Illustrations" group, select "Shapes". Select any shape you wish. I usually select a rounded rectangle. Your mouse pointer should then turn to cross-hairs. Select a spot on your work sheet and drag the cross-hairs across, down, up etc. to create your button. You'll notice that the "Drawing Tools" function above the "Format" tab will light up at the top of the page. Click on the down arrow and you will be given many options for the button. Once you have selected your design, right click on the button, select "Assign Macro" from the menu that appears. The "Assign Macro" dialogue box will appear. Select the name of the code (CopyRows) then click OK. All done.
This may seem a bit long winded, but it actually only takes a few seconds to do once you have the general idea. There are other ways of creating buttons (form controls, activeX controls) but this is just my preferred method.
I strongly suggest that you practice this in a copy of your work book first, just to get the "hang" of it all (and to test your creativity!).

Of course, this can also be done without the need for a button. You could use a "Worksheet_BeforeDoubleClick" event which means that once you double click on a quantity in Column F, the above code will execute.
Anyway, I think that I have bored you enough. Good luck with it all.
Cheerio,
vcoolio.

vcoolio · Answer

Hello Richard,
With the barcodes, there are two types that can be implemented:-
Code 39 and Code 128 barcodes.
I would say that the type 128 code would suit you the best.
Using it in VBA is a little involved but at the same time relatively easy to do, so, to save me writing an essay here, have a look at the following link to an article by Philip and Mynda Treacy which clearly explains how it is done in easy to understand terminology:-
http://www.myonlinetraininghub.com/create-barcodes-with-excel-vba
There are a couple of items (barcode fonts) that you'll need to download but these are all part of the article which shows you how.
So, make your next break a little longer than usual, carefully read the article and then test it out (again, in a copy of your work book first). The code is already done for you. You may only have to change the cell references to suit yourself.
Have fun with it!
Cheerio,
vcoolio.

Repeat row x times

8 replies

Similar discussions

Subscribe To Our Newsletter!