Split Workbook into Multiple Workbooks

[jackie]

Hi- I have a macro that currently generates commission statements for my
sales reps into a new workbook based on a list of names. I can either
generate one file with all statements or individual files for each statement.

I would really like to create district workbooks. I have 20 districts, each
with varying number of sales reps. I'd like 20 separate workbooks.

The macro that I currently have looks at column A (Rep Names) to create the
statements. Is there a way to look at column B (District #) to create
separate workbooks.


Right now I have to run the macro for district one. Paste the names for
district two and run again, paste the names for district 3 and run again, etc.

Thanks,

 

[Ron de Bruin]

Maybe this is I understand you correct?
http://www.rondebruin.nl/copy5.htm

Try this example
http://www.rondebruin.nl/copy5_3.htm

 

[George from Central Trains Birmingham UK]

Jackie

Have you written the Macro or recorded it ?

If you can programme in VBA I can send you a routine that will take a BIG
Workbook and split it various smaller workbooks based upon the generic names
of the worksheets in the BIG Workbook

George

 

[jackie]

Hi Ron-
I am trying to work through this to make work with what I have. I've gotten
to the part in your code where you copy the visible data. I need something
that will name the visible cells in column A only.

A piece of my old code looked something like this:
Set NameRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In NameRng.Cells
Template.Copy _
After:=NewWkbk.Sheets(NewWkbk.Sheets.Count)

I need NameRng now to refer to only the visible cells in column A after the
filter. If it makes a difference, I have filtered based on data in column B.

 

[Dave Peterson]

If there's always at least one visible cell in that filtered range:

For Each myCell In NameRng.Cells.specialcells(xlcelltypevisible).cells

If your filter may cause 0 visible rows (not including the header), I'd check
with something like:

Dim NameRngVis as range 'near the other declarations.
....

set namerngvis = nothing
on error resume next
set namerngvis=NameRng.Cells.specialcells(xlcelltypevisible)
on error goto 0

if namerngvis is nothing then
msgbox "No visible cells!"
exit sub '????????
else
for each mycell in NameRngvis.Cells
....

 

[jackie]

This particular macro I wrote with the help of my friends on this website.

Anything you can provide would be helpful.

Thanks!