Auto copy and insert a defined number of rows as defined in a cell

I don't know much about VBA but could probably work something out.
I need to produce carton labels from a spreadsheet using Word to mail merge 
and the labels need to include 1 of 10, 2 of 10 on them.  The number of 
labels required is dependant on a number which is included for each row of 
data in the spreadsheet.  What I am doing at the moment is copying each row 
and insert pasting the additional number of rows required. In the new rows I 
then add 1 of 10 in the first row, 2 of 10 in the second row etc. etc. This 
then enables me to perform the mailmerge.
I have around 300 label variants (300 different rows of data) which yield 
around 2500 labels and I have to do this every 3 weeks.  As you can imagine 
it takes a crazy amount of time and requires a lot of checking.
I'd guess there must be a much easier way that even someone of my moderate 
experience could execute but I need a few pointers to get me started.
Any advice greatly appreciated.
3/17/2010 10:54:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 24

Sub InsertRows()
'assumes column A contains the total number of rows required for each
'row of data
'assumes this cell can be overwritten with "1 of n"
'A3 taken as the first cell containing data
'assumes Sheet1 contains the data

'turn off screen updating for speed
Application.ScreenUpdating = False

'initialize variables
Dim rngEndcell As Range     'last occupied cell in column A
Dim rngInsrt As Range       'range of occupied cells in Column A
Dim L As Long               'number of rows to be inserted
Dim i As Long               'loop variable
Dim S As Long               'loop variable
Dim v As Long               'loop variable
Dim T As Range              'transient address of cell below which rows are

'get address of last occupied cell in column A
Set rngEndcell = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp)
'set the occupied range
Set rngInsrt = Range("A3", rngEndcell)
'initialise a loop variable with the number of rows
i = rngInsrt.Rows.Count

'step through the range from the bottom up
For S = i To 1 Step -1

'T holds the address of the cell below which rows are inserted
Set T = Range("A3").Offset(S - 1, 0)
'L holds the total number of rows required
L = Range("A3").Offset(S - 1, 0).Value

T = "1 of " & L
'perform the loop inserting rows and
'copying the initial row and setting the "1 of n" value
For v = 1 To L - 1
T.Offset(v, 0).EntireRow.Insert shift:=xlDown
T.Offset(v, 0).EntireRow.PasteSpecial xlPasteValuesAndNumberFormats
T.Offset(v, 0) = v + 1 & " of " & L
Next v

Next S

'get rid of crawling ants
Application.CutCopyMode = False

'select a finishing location for the cursor

Application.ScreenUpdating = True

End Sub

"Billy" <> wrote in message
> Hi
> I don't know much about VBA but could probably work something out.
>I need a few pointers to get me started.
> Any advice greatly appreciated.

3/18/2010 10:48:52 PM

Similar Artilces:

Copy Excel email addresses to GroupWise
I have an Excel worksheet that has a column that contains email addresses. I would like to copy some of the email addresses into GroupWise. When I select certain email addresses and copy and paste them into GroupWise. For some reason I get the email addresses in between the emails that I have selected. Examples: If I selected,, and and pasted into GroupWise, I would receive all of the addressesin the TO: f...

Accumulate Sales Numbers
Having trouble getting my hands around this problem. I receive sales numbers from stores every week, I have built a workbook that totals all the weekly numbers from each file on one sheet. I would like to have a worksheet for Month, Qtr, YTD, I would like these numbers to accumulate from the weekly #'s I bring in each week. My problem is I don't want to keep the weekly numbers every week, I'd like to zero them each week and bring in the new (adding to the totals on the other worksheets) Is this possible, well I'm sure it is, but how is it possible. Thanks for your hel...

Inserting comment depending on the Conditional formatting
Hi Is it possible to insert comment depending on the conditiona formatting.I mean if the color of the cell is blue the comment shown i " A " & if the color of the cell is green the comment shown is " B " similarly if the color of the cell is black the comment shown is " C . ( The color names which i had writted here r examples i dont know th real names of the colors which i have given in my spreadsheet) -- Little Maste ----------------------------------------------------------------------- Little Master's Profile:

Can you filter or sort by a cell's formatting? #2
Can you filter or sort by the formatting of a cell - these were formatted manually and do not have conditional formatting applied to them - thanks You can even use a UserDefined Function that can return the colorindex. Chip Pearson has a nice function at: Returning The ColorIndex Of A Cell If you're new to macros, you may want to read David McRitchie's intro at: MalibuinthePeg wrote: > > Can you filter or sort by the formatting of a cell - these were formatted > manually and do...

Increasing beyond 65,536 rows
Does anyone know how I can increase the number of rows in an Exce worksheet ----------------------------------------------- ~~ Message posted from ~~View and post usenet messages directly from "speedy" <> wrote... >Does anyone know how I can increase the number of rows in an Excel >worksheet? You can't. Excel provides 65536 rows unless you use Excel 5/95 or even earlier versions, in which case it provides only 16384 rows. ...

How to breakthrough row number limitation
Hi all, The default row number limitation for each sheet is 65,536. Is it possible to increase it up to 100,000 ? Your help would be appreciated. Billy No. You'll have to use multiple columns or multiple worksheets (or another app, like Quattro Pro or Access). In article <OcGz2DVHFHA.3612@TK2MSFTNGP09.phx.gbl>, "Billy Leung" <> wrote: > > The default row number limitation for each sheet is 65,536. Is it possible > to increase it up to 100,000 ? Your help would be appreciated. Hi JE McGimpsey, thank you so much for...

Row Level Versioning using Read Committed Isolation
We have Read Committed Isolation enabled and I was interested in more closely monitoring TempDB usage. We do NOT have Snapshot Isolation enabled, only Read Committed Isolation. In looking at sys.dm_db_file_space_usage, the column version_store_reserved_page_count specifically, does that relate to the usage of the version store when Read Committed Isolation is enabled, or only when Snapshot Isolation is enabled, or both? Second question. It appears that according to sys.dm_db_file_space_usage, the column version_store_reserved_page_count specifically, that the page counts are activ...

Linking cells in a worksheet to other worksheets in a workbook
I'd like to be able to link a cell in one worksheet to another worksheet, so that when I click on the cell in the first worksheet, the other worksheet opens up. I have a spreadsheet where the first page (worksheet) has a listing of items contained in other tabs (worksheets). I'd like to be able to click on the first worksheet "index" and have the appropriate worksheet pop open with the contents of the index. Thanks for your responses! (In Excel 2003) Select a cell to which you would like to add a link. Select Insert->Hyperlink. Click the "Place in this doc...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

Do a calculation in cells with text data format
I have a few columns of cells having a mixed data format of number and text. Is it possible to convert the first row of numbers in text data format for further calculation? Your guidance to accomplish it is appreciated. Thanks, Ray Example? -- Regards, Peo Sjoblom "Ray" <> wrote in message news:ei$Jbmy$FHA.216@TK2MSFTNGP15.phx.gbl... > I have a few columns of cells having a mixed data format of number and text. > Is it possible to convert the first row of numbers in text data format for > further calculation? Your guidance to accomplis...

Hide row code help
I am using the following script to hide or unhide rows. How do I code the offset to only work when a row is being unhid. If we cant do this only offset when it is being hidden or unhidden? Thank you Private Sub Worksheet_Change(ByVal Target As Range) 'Application.EnableEvents = False' not necessary If Not Application.Intersect(Target, Range("B9")) Is Nothing Then Rows("10:11").Hidden = IIf(UCase(Range("B9")) = "", True, False) 'spelling ActiveCell.Offset(-6, 0).Select End If 'Application.EnableEvents = False ...

Changing of Cell protections after saving Excel File (2002)
This problem occurs when I protect a document using a macro 4.0 function: =PROTECT.DOCUMENT(TRUE,,,TRUE,TRUE). When I use the function within a macro4.0 macro, on an original file, everything works fine. The sheet has unlocked cells, and when the sheet is protected, it allows me to access those cells. But if I save the file, or another name, then the fun begins. The enable selection of the sheet( view codes) has gone from 0-xlNoRestrictions to -4142- xlNoSelection. This locks me out of doing anything in the sheet. When I unprotect and then re-protect the sheet using the T...

how do I change cell references automatically in formulas
In Excel 2000, I have data in 80 rows and 10 columns. Each week I add a new row. I have a separate chart for each column with the data range from the first row to the last.. Each week I have to change the data range to reflect the new last row for each chart. Is there someway I can do this automatically? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions _______ "jnw3" <> wrote in message news:8A3551F9-FBC6-4841-95B4-618AB1893190@micros...

Cell can left indent; anything for right side of cell?
Is there a way to leave more space between the end of a word and the right side of the column? I indented the left side of the column by using the indent option set to "1" in the alignment tab of the formatting box. Besides adding an extra column to the end of the row and making it very small in width and extending the border to that extra "spacer" column, is there a way to make this space? My spreadsheet is done and if I have to do this, I'll have tons of work re-writing code. I was going to use Word for the final presentation of the data, but it didn't work ou...

Want to see only specific number of records on a form
Hi I have a form with default view as "Continuous Forms". When I openthis form I only want to see the records equal to Recordcount i.e. ifI have 3 records I should only see three records. I dont want to seean extra one record which is blank. The easy way is simply to set the allow additions to "no".(you will still be able to add records via a code, or perhaps a "add" button you provide.Or, perhaps you don't even need add record ability...Anyway...give the above a will remove that extra "new record" entry....-- Albert D. Kallal (Access MVP)...

Defining same name for cells in different sheets
Does anyone know the answer to this one? I want to give the same range name to the same cell reference in a series of worksheets. I find I can do this by pre-defining all my range names on a "master" worksheet and making several copies of the sheet (try it, it works!) But let's say I have done this, and entered all my data on the sheets I have created, and suddenly realize I need another range name. I haven't found any way to define a new range name and apply it to the same range on several sheets. This is not the same as a 3-D reference, which I tried. (3- d referenci...

Alternating cell shading colors for every other merged cell
I am trying to get every other merged row to be a certain cell shading color. There is some discussion in the newsgroups about alternating colors for every row, but in my case I had merged cells and the formulas given didn't turn out right on my tables since they were based on the row numbers. Does anyone know how to have alternating cell colors for merged rows? (the merged rows are random sizes) Thanks. Not an answer to your question I'm afraid, but just for info, most people in here steer clear of merged cells like the plague - they tend to cause far more problems than they ever se...

increasing numbers in sreadsheet
I need prepare full list with numbers, starting from 129680 and up to 129779 (in some column): 129680 129681 129682 .... 129779 what is formula? If you want a formula solution, enter your first number in A1 then in A2 enter: =A1+1 then copy this down for as many cells as you require. Alternatively you can fill the values down - enter your first value in A1, then highlight cells from A1 down (as many as you need - hold down <SHIFT> while pressing <PageDown>, then release <SHIFT>), then Edit | Fill | Series and specify Linear with a Step Value of 1, click OK. Hope th...

Go To an address specfied in a cell
Hello Folks, Does anyone know how I can move the cursor to a cell, the address of which is specified in another cell? Here is the scenario. I enter a list of hours worked in a specfic week on a data entry sheet. I hit a button and the values are copied to a data summary sheet, the position depends on the Week No., the first cell is specfied as the address "Data!29" for Week 5. I reckon I can handle a recorded macro to copy and paste the data but how do I locate the correct start cell? I have tried copying and pasting into the GoTo box but that doesn't work. Data!J29 Wee...

how to write a number with 0
Hi how to write number starting with 0 for eg. i want to write number 021495566 i had tried but it gives 21495566 pls do the needfu -- NITESH ----------------------------------------------------------------------- NITESH G's Profile: View this thread: Hi Nitesh, Try using the custom format: 000000000 --- Regards, Norman "NITESH G" <> wrote in message news:NITESH.G.1y475y_1131346800.45...

Script to auto fill Creditor doc # in Auto Invoice
Hi. We are setting up RMS HQ + 42 stores to integrate into GP using Connect Pro which is going great so far. Connect Pro integrates purchases into Shipment only and we then have to Auto Invoice all receipts by supplier to process the roughly 5,500 invoices at store level per month. We can run a macro to populate the creditor doc # at auto invoice stage, but is there any other utility / sql script / vb script available which we can use to bring the PO Title across from RMS tables (RMS po # = Batch ID) without having to manually process 5,500 invoices or use the macro? This would t...

Help with displaying the contents of the last populate cell.
I have numerous sheets within a book where all cells in column C in all sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you reference both columns P and R hold a monetary value and are formatted as Currency. Is there a way that cell D1 can automatically be populated with the contents of the last cell in column C that has a value in it. E.G. Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200. Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250. Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900. Any h...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Excel 97 Worksheet Protection and cell colour
Hi there, One of our users has setup a worksheet will a small range of cells that are locked (they have formulas in them), he then protects the sheet. He then wants to change the colour of some of the other cells, these cells are not locked, but he cannot change the colour of the cells. Is there an obvious solution? Cheers, Andy Hi AFAIK you can't do this in Excel 97 without first removing the protection -- Regards Frank Kabel Frankfurt, Germany andy wrote: > Hi there, > > One of our users has setup a worksheet will a small range of cells > that are locked (they h...

Copy NDRs to public folder
All, I have moved my postmaster mailbox into a mail-enabled folder, which is working fine. However, I cannot get Exchange to send copies of NDRs to the postmaster mailbox. If I select any other "true" mailbox, it's fine. Isn't this supported or am I missing something? Any help appreciated! -- Kind regards, Erik Cheizoo eXcellence & Difference - we keep your business running ============================================ Always test in a non-production environment before implementing Guidelines for posting: ======================...