So I'm trying to print invoices so when I print 100 copies, the C4 cell changes to consecutive numbers...ie print 1 is 001, print 2 is 002 and so on.

How do I set this up?

12/10/2008 10:17:13 AM

My 90 seconds of googling says you are going to need a macro to do this.

12/10/2008 10:41:37 AM

hello tech talk

12/10/2008 10:42:59 AM

First set a custom format in cell C4 as "000". Under Format > Cells, like follows


then open up the Macro editor. Tools > Macro > Visual Basic Editor

Then doubleclick the sheetname in the left tree that you want to print, and paste the following into the editor

Sub PrintCopies()
    Dim i As Integer
    For i = 1 To 100
        ActiveSheet.Range("C4").Value = i
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Next i
End Sub

12/10/2008 12:50:15 PM

Oh man, thank you so much.

I hardly ever use excel for my job, and no one in the office even knew what a macro was for excel...

Silly scientists.

Thanks!!!!

12/10/2008 1:43:10 PM

Sorry for the thread hijack, but I have what is probably a simple excel problem, but I am a n00b so I don't know how to get what I want.

Let's say I have a file with four separate columns for an address--one for the street number, the next for the street name, the next for the suffix, the next for the apartment number.

Instead of four separate columns, I need the information in one column.

(ex. instead of 123, Main, Street, Apt 201... I need 123 Main Street Apt. 201)

I found some info on google on cell merging, but I wasn't able to get it to work right. Am I on the right track?

12/10/2008 1:58:58 PM

=A3&" "&B3&" "&C3&" "&D3


Paste that formula into E3 and copy down for all the cells

Then highlight the column of the concatenated data you just made, copy, paste values, then delete all the old columns you don't need anymore.

Obviously, if you're going to be doing this over and over again you can manage the data in a different way. But this will work as a one time thing

The ampersand is the concatenation operator for text in excel.

12/10/2008 2:25:56 PM

in addition to ^ method, there is also a "concatenate" function, that works basically the same, but the formula looks a bit different (in my opinion, it's a bit cleaner b/c it's not littered with &s)

=CONCATENATE(A3," ",B3," ",C3," ",D3)

12/10/2008 2:40:39 PM

Thanks. I'll try it out.

12/10/2008 3:32:59 PM