New Spreadsheet Created Based on Value Column "O" (alpha)

[Dandelo]

I'm creating invoices using Excel 2003 (Office Pro) in Windows XP SP2

My summary spreadhseet contains names in Column O (Alphabetic "O")

For duplicate values in Column "O" of my summary spreadsheet, I need to
create a new Invoice (spreadsheet) based on duplicate Values in column "O".

I know enough to sort first by Column "O" on the summary spreadsheet, and I
know how to add new spreadsheets, but I don't know how to generate a new
spreadsheet based on a change in the value in column "O".

Example names from Column "O"

Smith John
Brown Harry
Green Rita Mae

Can someone provide some ideas for this?

 

[JLGWhiz]

Do you want to add new workbooks or new worksheets? Spreadsheet is a
general term that can mean eaither of the other two or both.

 

[Dandelo]

I would like to add new worksheets ( instead of new workbooks)

 

[JLGWhiz]

The best I can offer from the information you have provided is to suggest
using the Worksheet Change event to accomplish what you want to do. It would
look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("O:O"), Target) Is Nothing Then
Worksheet.Add
End If
End Sub

Caution: This code, when put in the worksheet code module, will execute for
ANY change in column O. That includes new entry, delete, plus or minus. You
can narrow it down some by using If ... Then statements to set parameters
that will control when the code will execute. But first be sure this is the
route you want to take.

 

[Dandelo]

Thank you. I'll add this to the macro I'm working and see how it works.

 

[JLGWhiz]

There was a typo in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("O:O"), Target) Is Nothing Then
Worksheets.Add After:=Sheets(Sheets.Count)
End If
End Sub