Hello,

Now I understand how it works, thanks for help me out! I was putting a
blank row between the header rows and the data, that is why it wasn't
working, now it works fine, thanks so much!

Have a great day!

Dejan

"Bernie Deitrick" wrote:

Dejan,

Put a blank row between the top row(s) and the row of headers. You should only have ONE row of
headers - if you have more, then insert the blank line above the last row of header values. Then
select a single cell within your data table, and run the macro again.

HTH,
Bernie
MS Excel MVP


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

I need your help again, please. I got the worksheet to copy the subtotal
with your change of code. But I don't understand how it's suppose to copy
the first two rows.

The first row contains the date and the second conatins the headers. So how
can i get it to take both of the rows along with the appropriate account
information and subtotal.

Also one more question, is there a way for the code to first copy the two
rows as they are and only paste special the account information and the
subtotal line, so how would I get it to perform the task in two chuncks. Or
is there a way to have a template sheet and only to have the account
information pasted let's say on and after line 5 as paste sepcial, so there
would be this sheet let's say Sheet2 and it would already be formated and
have the header information. Then the macro would just paste special the
account information and subtotal ater line 5 and on, rename and continue with
next one.

Thanks again, I really appreciate you helping me.

Dejan

Thanks Bernie

"Bernie Deitrick" wrote:

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.