Hello Bernie,

That works great, now these are my next issues, would appreciate your help.

How would I get the macro to copy more than one row to the top of each
sheet. Then i also need copied the totals formulas at the bottom of the
database onto each sheet. Maybe if it asked me what row was the totals
located or if the totals row with formulas had then number 1 always in the
first a coloumn.

Thank you so much Bernie!

Dejan

"Bernie Deitrick" wrote:

Dejan,

The macro below will create new files in the same folder as the workbook with the database.

If you wanted to email the spreadsheet after it is created, you could do something like this after
the SaveAs line:

ActiveWorkbook.SendMail ", "This is the Subject line"

If you wanted to print each of the spreadsheet after they are created (to mail the invoices) you
could do something like this after the SaveAs line:

ActiveSheet.PrintOut

Of course, you would need a table of email addresses that had the export key as well so that you
could match the email to the exported file. That could be done in code as well: your whole process
could be a one-button click to fire the macro.

HTH,
Bernie
MS Excel MVP


Sub ExportDatabaseToSeparateFiles()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")


Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name = myShtName Then
Exit Sub
Else
mySht.Move
ActiveWorkbook.SaveAs "Workbook " & ActiveSheet.Name & ".xls"
ActiveWorkbook.Close
End If
Next mySht
End Sub



"Dejan" wrote in message
...
Hello Bernie,

What I am doing is getting raw data from a shipping manfiest. Then what
happens is I format the manifest in a specific order and define two Vlookup
names. Then what happens is there are 238 accounts so hence the 238 sheets.
All 238 sheets pull their waybill's from that sheet then the Vlookup formula
gets the rest of the information for each waybill, quantity, cost etc.. on
each of the tabs. So what I have to do each time is print off the Master
sheet which has subtotals of all the sheets and then I painstakingly CTRL and
click through the workbook and highlight all the sheets that contain any
information. Then I copy these sheet along with the master and the subtotal
sheet to a new file. After i go through each sheet and click through the
Auto-Filter so that It only shows the rows with values in them, i have 70
rows that have a formula in them, and so I use the Auto-Filter to get only
the non-blank rows, i do that to each sheet, then It's ready to be emailed to
our customers. The whole process takes a long time. I know that you can use
the Filter on the master sheet but the whole thing is that the customer is
only suppose to see what they ordered, they only get their sheet. Hope
that's clear enough, I can send a sample if you would like.

Thanks for your help again...

Dejan

"Bernie Deitrick" wrote:

Dejan,

It sounds like you would be better off removing the 238 sheets and just
using the master sheet with a filter and as the data source for a pivot
table.

Describe what you are doing, and perhaps we can improve the performance of
the whole thing.

HTH,
Bernie
MS Excel MVP


"Dejan" wrote in message
...
Hello,

Problem #1: I have a giant workbook with 240 tabs. Each of the 238 sheets
contain specific information to them. This information is put in from a
master sheet via an Array formual, when I put in a new sheet all the other
sheets gain their infromation from the master sheet. Each of the sheets
has
about 70 lines and based on how much activity there was on a certain
account
the lines can range from anywhere from no acctivity to all 70 lines. So
what
I have done on the last coloumn on each sheet is put in a custom Auto
Filter,
to show me only if the cell is greater than 0. My problem comes when I
enter
a new set of data I have to go to each sheet and click on the filter and
push
custom and ok, it takes a long time. I am sure there must be a quicker
way
to ask all sheets to recalculate the Auto Filter. Please help

Problem #2: With all of these 238 sheets the first sheet contains
subtotals
from all sheets and the second sheet conatins all the data for all the
other
238 sheets. So what I have to do is go one by one sheet and copy only the
ones that have balance greater than 0 to a new sheet, is there any way to
have the sheets with a blanace of greater than 0 copied automatically.

Thanks in advance, any suggestions are appreciated.

Thanks.