Parse data from a spreadsheet to individual workbooks?

[scorpiogirl]

Each month I have a report that shows all billing details for each customer. I customize the data quite a bit and then I have a time consuming process of separating out each customer's detail into a sheet for them.

I wonder if there is a way to parse each customer's data into a workbook of their own? each customer has a unique identifier (column A) - maybe that could be the file name since it is a single word and unique and no special characters? there is a black row which separates each customer although I could make this area anything... I could make that black row area the row names instead if that is easier. I already have a macro to copy the top row and place it between each new agency.

so, for the part I am hoping to find help... each month, I copy the items for each agency in columns B-K and paste that into a detail sheet for each agency. I have also attached a sample of the billing sheet.

please keep in mind... the number of rows for each customer varies. some customers have 2 rows and some have 90 rows.

Any help would be appreciated. Please ask any questions I may not have covered.

if needed, the macro I currently use to insert the black row between each agency
Sub InsertRowAndHeaderChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then Rows(lRow).EntireRow.Insert
Sheet1.Range("A1").EntireRow.Copy
Next lRow
End Sub

[JOHN H. DAVIS]

I wasn't sure if you wanted new workbooks or new sheets. So I added the Invoice Detail Sheet in with the Billing Sheet. Maybe this can get you started. Activate the Billing sheet and run.

Please Login or Register  to view this content.

[scorpiogirl]

Hi John,

Thank you so much for the help - I really appreciate it! This was perfect timing as I am actually working on this monthly task right now... I loathe this part of my job. I ran the macro but I received an error.... "Run-time error '9': Subscript out of range". It inserted one worksheet with no data in it and that was all... then the run-time error appeared.

thanks, tina

[scorpiogirl]

oh and sorry, you were right. I separate the detail into separate workbooks for each customer.... not into sheets in an existing workbook.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

I renamed the Workbook starting with Invoice Detail to just Invoice Detail.xlsx. Then I activated the billing workbook, and ran the macro. The workbooks created will remain open. If you want them closed then I'll need to revise. However, try first too see if it does what you want.

[scorpiogirl]

Hi John,

definitely a step in the right direction! A new workbook was created for each customer so that was brilliant in itself! The contents of each worksheet were blank. The top three rows in the new worksheets were highlighted but no data at all. I added a new sample sheet just in case that helps. In the sample 'fake-Aug2013' I would manually copy D4:M16 into 'billing template_FAKE' into A4:J16 and then save that into a sheet and I typically use the word in column B of the 'fake-Aug2013' as the file name (solutions for the first group). If this is something that just can't be done then I completely understand but I figured I would check

thanks!
Tina

[AB33]

How are these two books related?
On your description, you want to create individual workbook for each unique row in column A. I understand this bit, but where is the second book (Invoice) come in to play? Individual workbooks will have their own data with a heading and everything else, and the source of these data come from the master sheet( Billing book in the first attached).

[JOHN H. DAVIS]

Tina:

You need to change the name which identifies the template:

Please Login or Register  to view this content.

Then select fake-AUG2013.xlsx and run the macro>