need help with numbering NCR forms

teresajorgenson · #1 05-24-2013, 06:35 AM

Hi all:

I need some help with numbering NCR forms I'm trying to print. In cell F12 I have a "form number" that begins with FE-3601 and I need to print and number them consecutively from there. ie, FE-3601, then FE-3602, FE-3603, etc. I need the number to repeat three times for each sheet of the NCR paper! Any assistance would be appreciated. I have used Excel quite a bit but never worked with Macros.

macropod · #2 05-25-2013, 11:50 PM

Hi Teresa,

What you seem to be describing is a requirement to print 3 copies of a form, increment the #, then print another 3 copies, starting a one specified number and ending at another. Correct?

teresajorgenson · #3 05-28-2013, 07:35 AM

Yes! I need the same number repeated three times for each color of the NCR--white, yellow, pink. So FE-3601 is my first number, then FE-3602, FE-3603, and so on. The number needs to appear in cell F-12. Thanks!

BobBridges · #4 05-28-2013, 09:29 AM

I'm missing something, though. Are you saying that you have a worksheet representing a blank NCR form, and you're asking how to get a macro to print off the worksheet n*3 times while renumbering it appropriately for each copy? Or do you have many sheets (one for each form) and you want to know how to get each sheet to have the right number on it before you start printing them all?

macropod · #5 05-28-2013, 04:38 PM

Hi Teresa,

Assuming you already have your NCR sheets sorted, you could use a macro like:

Code:

Sub PrintNCRSeries()
Dim StrSeries As String, i As Long, j As Long, k As Long
With ActiveSheet
  With .Range("F12")
    If InStr(.Value, "-") > 0 Then
      i = Split(.Value, "-")(1) + 1
      j = Split(.Value, "-")(1) + 10
    End If
  End With
  StrSeries = InputBox("What is the range to print? (eg 1-10)", _
    "NCR Print Manager", Format(i, "0000") & "-" & Format(j, "0000"))
  If StrSeries = "" Then Exit Sub
  i = Split(StrSeries, "-")(0)
  j = i
  If InStr(StrSeries, "-") > 0 Then j = Split(StrSeries, "-")(1)
  For k = i To j
    .Range("F12").Value = "FE-" & Format(k, "0000")
    .PrintOut Copies:=3
  Next
End With
End Sub

teresajorgenson · #6 05-29-2013, 09:05 AM

Thanks Paul! The forms come in sets of three--white, yellow, pink. So I need each number to repeat three times, then go on to the next number. I am starting this print at a number where someone else left off so it doesn't start at logical number

My first number will be "FE-3601" and continue up from there.

macropod · #7 05-29-2013, 04:03 PM

Have you tried the macro?

teresajorgenson · #8 05-29-2013, 04:36 PM

Not yet; been swamped today with other printing. Hope to try it tomorrow--may need more assistance! I've used MS Office a lot but never used the macros.

teresajorgenson · #9 05-30-2013, 07:25 AM

OK Paul, I am a super dummy when it comes to macros; do I open the original file and select cell F12 then record the macro there? I have never worked with macros; understand how they work but not sure how to apply them!

macropod · #10 05-30-2013, 02:03 PM

To install the macro:
• open your workbook and press Alt-F11. This opens the VBA editor.
• to the left you'll see a panel with your workbook name. Click on that.
• choose Insert|Module.
• copy and paste the macro I posted into that module.
• press Alt-F11 again. This closes the VBA editor.
The macro is now installed. Save your workbook, making sure you save it as a macro-enabled workbook (xlsm format). From now on, to use the macro:
• press Alt-F8 and choose PrintNCRSeries>OK.
• input the form range (eg 3601-3651), then press OK.
The macro defaults to one more than whatever number is already in F12, ending 10 forms further on. So, if you save the workbook after printing, or you simply run the macro again before closing the workbook, the next sequence will offered the next time you start the workbook. If you'd prefer a different default sequence length, simply change the '10' in the macro.

If your system's macro security is set too high to allow macros to run, click on Developer>Macro Security and set it to 'Disable all macros with notification'. With this setting, you'll be asked whether to enable the macro each time you open the workbook, without the risk of 'macro viruses' in workbooks others might send you being able to run without your say-so.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
forms	mrmagoo	Word	2	06-28-2012 01:25 PM
Forms	textboom	Word VBA	0	02-23-2011 07:51 AM
Document unique numbering / forms	JamesF	Word	0	11-22-2010 03:52 AM
Forms help	benji842	Word	0	06-24-2010 06:04 AM
Forms	oo7juk	Word	0	10-09-2009 01:55 AM