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.
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:-
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.
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.
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.
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:-
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.
