Repeat row x times [Closed]

- - Latest reply: vcoolio
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
- Mar 1, 2017 at 03:55 AM
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,
See more 

8 replies

Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
207
0
Thank you
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.
0
Thank you
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,
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
207
0
Thank you
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.
0
Thank you
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
-
Just a thought....

You have put your example in drop box. Could this be causing the example not to work.

Thank you again.
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
207
0
Thank you
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.
0
Thank you
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.
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
207
0
Thank you
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.
You have been a great help...Thank you very much.

I do have 1 more question.
I know how to make a barcode in microsoft word. I cant get it to repeat and follow the excel list you have created. It seems to only want to make the first row.

Do you know how to make it repeat labels and change when column 1 changes and repeat again?

Basically make a barcode label for everything on your sheet 2 you created.

Thank so much
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
207
0
Thank you
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.