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.

Hi Dejan,

Are you aware of 3-D references like for SUM of a specific cell range
from each sheet of range of sheets (left to right), you can make up a
name to the right side (and or left side) and insert your news sheets before
you high end sheet tab.

Since I goofed last time, so I will refer you directly to the HELP file
Using the Answer Wizard (search) in Excel Help
3D sheet references
then look at "Refer to the same cell or range on multiple sheets by using a 3-D reference"

Also you might make use of the following if automatic recalculation does
not take place.
Ctrl+Alt+F9 Recalculates all cells on all worksheets in all open workbooks.
Ctrl+Alt+Shift+F9 For Excel 2002 will rebuild all dependency trees.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"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.

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.

Much better answer, I forgot to mention that I didn't really
understand the problem. So was only making some suggestions
that may or may not have any bearing. But your suggestion is the
obvious solution why make a lot more work and double the size
the of the workbook as well.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message news:%
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.

"Dejan" wrote [clipped]
Problem #1: I have a giant workbook with 240 tabs. Each of the 238 sheets
Problem #2: With all of these 238 sheets the first sheet contains

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.

Dejan,

I have a macro that will extract the data from the database to separate
sheets (or separate workbooks) that will allow you to bypass the clicking
and filtering. When I get into work in the morning, I will post it.

HTH,
Bernie
MS Excel MVP


"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.

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.

Thank you very much, i will give this a try and let you know how I made out.

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.

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.

Dejan,

Set up your totals using the SUBTOTAL function, using a formula like

=SUBTOTAL(9,B10:B1000)

Include all of your data in the range: when the data block is filtered, you will just sum the values
that are still visible.

Put the SUBTOTAL formulas into a single row separated from the bottom of your database by _at least
one completely blank line_.

Then we can simply change the line

.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")

to

myCell.Parent.Cells.SpecialCells(xlCellTypeVisible ).Copy
mySht.Range("A1").PasteSpecial xlPasteValues

That will pick up extra header rows, and the row of SUBTotals from the bottom - converted to values.

HTH,
Bernie
MS Excel MVP


"Dejan" wrote in message
...
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.