AUTO FILTER NOT CHANGING RESULTS

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.
0
dejan (21)
9/28/2005 8:41:02 PM
excel.misc 78881 articles. 5 followers. Follow

25 Replies
504 Views

Similar Articles

[PageSpeed] 51

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" <Dejan@discussions.microsoft.com> wrote in message news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
> 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.


0
9/28/2005 11:34:48 PM
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" <Dejan@discussions.microsoft.com> wrote in message 
news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
> 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. 


0
Bernie
9/28/2005 11:40:13 PM
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" <Dejan@discussions.microsoft.com> 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


0
9/29/2005 12:00:39 AM
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" <Dejan@discussions.microsoft.com> wrote in message 
> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
> > 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. 
> 
> 
> 
0
dejan (21)
9/29/2005 12:11:06 AM
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" <Dejan@discussions.microsoft.com> wrote in message 
news:65B361F6-9F3F-4CF8-A566-7803D25094AB@microsoft.com...
> 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" <Dejan@discussions.microsoft.com> wrote in message
>> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
>> > 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.
>>
>>
>> 


0
Bernie
9/29/2005 1:05:33 AM
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 "dejan@somwhere.com",  "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(Before:=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" <Dejan@discussions.microsoft.com> wrote in message 
news:65B361F6-9F3F-4CF8-A566-7803D25094AB@microsoft.com...
> 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" <Dejan@discussions.microsoft.com> wrote in message
>> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
>> > 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.
>>
>>
>> 


0
Bernie
9/29/2005 12:27:15 PM
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 "dejan@somwhere.com",  "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(Before:=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" <Dejan@discussions.microsoft.com> wrote in message 
> news:65B361F6-9F3F-4CF8-A566-7803D25094AB@microsoft.com...
> > 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" <Dejan@discussions.microsoft.com> wrote in message
> >> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
> >> > 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.
> >>
> >>
> >> 
> 
> 
> 
0
dejan (21)
9/29/2005 12:34:05 PM
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 "dejan@somwhere.com",  "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(Before:=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" <Dejan@discussions.microsoft.com> wrote in message 
> news:65B361F6-9F3F-4CF8-A566-7803D25094AB@microsoft.com...
> > 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" <Dejan@discussions.microsoft.com> wrote in message
> >> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
> >> > 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.
> >>
> >>
> >> 
> 
> 
> 
0
dejan (21)
9/29/2005 2:19:01 PM
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" <Dejan@discussions.microsoft.com> wrote in message 
news:79774AAC-DA5C-46EC-9D5C-2CBA7445D06C@microsoft.com...
> 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 "dejan@somwhere.com",  "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(Before:=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" <Dejan@discussions.microsoft.com> wrote in message
>> news:65B361F6-9F3F-4CF8-A566-7803D25094AB@microsoft.com...
>> > 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" <Dejan@discussions.microsoft.com> wrote in message
>> >> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>> 


0
Bernie
9/29/2005 2:34:44 PM
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" <Dejan@discussions.microsoft.com> wrote in message 
> news:79774AAC-DA5C-46EC-9D5C-2CBA7445D06C@microsoft.com...
> > 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 "dejan@somwhere.com",  "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(Before:=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" <Dejan@discussions.microsoft.com> wrote in message
> >> news:65B361F6-9F3F-4CF8-A566-7803D25094AB@microsoft.com...
> >> > 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" <Dejan@discussions.microsoft.com> wrote in message
> >> >> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
> >> >> > 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
dejan (21)
9/29/2005 3:46:24 PM
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" <Dejan@discussions.microsoft.com> wrote in message 
news:4A3B44C6-95AF-485B-8288-8DDDD73FE7E5@microsoft.com...
> 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" <Dejan@discussions.microsoft.com> wrote in message
>> news:79774AAC-DA5C-46EC-9D5C-2CBA7445D06C@microsoft.com...
>> > 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 "dejan@somwhere.com",  "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(Before:=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" <Dejan@discussions.microsoft.com> wrote in message
>> >> news:65B361F6-9F3F-4CF8-A566-7803D25094AB@microsoft.com...
>> >> > 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" <Dejan@discussions.microsoft.com> wrote in message
>> >> >> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
>> >> >> > 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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Bernie
9/29/2005 3:54:14 PM
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" <Dejan@discussions.microsoft.com> wrote in message 
> news:4A3B44C6-95AF-485B-8288-8DDDD73FE7E5@microsoft.com...
> > 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" <Dejan@discussions.microsoft.com> wrote in message
> >> news:79774AAC-DA5C-46EC-9D5C-2CBA7445D06C@microsoft.com...
> >> > 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 "dejan@somwhere.com",  "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(Before:=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" <Dejan@discussions.microsoft.com> wrote in message
> >> >> news:65B361F6-9F3F-4CF8-A566-7803D25094AB@microsoft.com...
> >> >> > 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" <Dejan@discussions.microsoft.com> wrote in message
> >> >> >> news:2D73521E-0DA4-49CF-AB46-50DE7BAFD0CE@microsoft.com...
> >> >> >> > 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.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
dejan (21)
9/29/2005 4:10:01 PM
Dejan,

> working, now it works fine,
Great!

> thanks so much!
You're quite welcome.

> Have a great day!
You, too.

Bernie
MS Excel MVP


0
Bernie
9/29/2005 4:14:03 PM
Hello Bernie!

Sorry just one more problem, I was running the Macro on a actual sheet and I 
got a this error:

Run-time error '1004':

Cannot rename a sheet to the same name as another sheet, a
referenced object library or workbook refreenced by Visual Basic.

It creates the first sheet fine, when it gets to the second customer that is 
when it screws up this is the macro:

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(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
      mySht.Range("A1").PasteSpecial xlPasteValues
      mySht.Range("A1").PasteSpecial xlPasteFormats
    mySht.Cells.EntireColumn.AutoFit
    .AutoFilter
End With
Resume
SheetExists:
Next myCell


End Sub

Here are some of the values from the first few coloumns:

A              B              C              D              E
ACCOUNT	INVOICE	INV DATE	STR	FBY
10321	5173728	8/9/2005	5	59
10321	5175563	8/4/2005	5	51
10321	5175736	8/11/2005	5	69
10321	5175804	8/5/2005	5	59
10321	5176748	8/11/2005	5	59
10321	5176751	8/11/2005	5	59
10321	5178686	8/19/2005	5	69
10321	5179033	8/25/2005	5	69
10321	5179887	8/25/2005	5	69
10322	3528294	8/19/2005	3	69
10322	4347692	7/27/2005	4	69
10322	4349443	8/8/2005	4	69
10322	4352698	8/24/2005	4	69
10322	5169595	7/26/2005	5	57
10322	5173816	7/26/2005	5	57
10322	5173834	7/26/2005	5	59
10322	5173966	7/26/2005	5	59
10322	5175808	8/5/2005	5	69
10322	5175888	8/17/2005	5	69
10322	5176031	8/8/2005	5	69
10322	5178039	8/17/2005	5	69
10322	5178905	8/23/2005	5	69
10322	5179328	8/23/2005	5	69

So once it finishes doing 10321, it brings up the error, any idea what's 
wrong, thanks for all your help.

Dejan.
"Bernie Deitrick" wrote:

> Dejan,
> 
> > working, now it works fine,
> Great!
> 
> > thanks so much!
> You're quite welcome.
> 
> > Have a great day!
> You, too.
> 
> Bernie
> MS Excel MVP
> 
> 
> 
0
dejan (21)
9/29/2005 6:37:11 PM
Dejan,

Your key values are numbers, so change

myName = Worksheets(myCell.Value).Name

to

myName = Worksheets(CStr(myCell.Value)).Name

Worksheets can take either a number or a string - if it uses a number, it looks for that number 
sheet (the 10321st sheet) rather than one with that sheet name.

Sorry about that - I wrote the base code to work with alpha-numeric keys.

HTH,
Bernie
MS Excel MVP


"Dejan" <Dejan@discussions.microsoft.com> wrote in message 
news:573BA99E-92DD-4DD5-898C-0C2CB9E127BE@microsoft.com...
> Hello Bernie!
>
> Sorry just one more problem, I was running the Macro on a actual sheet and I
> got a this error:
>
> Run-time error '1004':
>
> Cannot rename a sheet to the same name as another sheet, a
> referenced object library or workbook refreenced by Visual Basic.
>
> It creates the first sheet fine, when it gets to the second customer that is
> when it screws up this is the macro:
>
> 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(Before:=Worksheets(1))
> mySht.Name = myCell.Value
> With myCell.CurrentRegion
>    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
>    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
>      mySht.Range("A1").PasteSpecial xlPasteValues
>      mySht.Range("A1").PasteSpecial xlPasteFormats
>    mySht.Cells.EntireColumn.AutoFit
>    .AutoFilter
> End With
> Resume
> SheetExists:
> Next myCell
>
>
> End Sub
>
> Here are some of the values from the first few coloumns:
>
> A              B              C              D              E
> ACCOUNT INVOICE INV DATE STR FBY
> 10321 5173728 8/9/2005 5 59
> 10321 5175563 8/4/2005 5 51
> 10321 5175736 8/11/2005 5 69
> 10321 5175804 8/5/2005 5 59
> 10321 5176748 8/11/2005 5 59
> 10321 5176751 8/11/2005 5 59
> 10321 5178686 8/19/2005 5 69
> 10321 5179033 8/25/2005 5 69
> 10321 5179887 8/25/2005 5 69
> 10322 3528294 8/19/2005 3 69
> 10322 4347692 7/27/2005 4 69
> 10322 4349443 8/8/2005 4 69
> 10322 4352698 8/24/2005 4 69
> 10322 5169595 7/26/2005 5 57
> 10322 5173816 7/26/2005 5 57
> 10322 5173834 7/26/2005 5 59
> 10322 5173966 7/26/2005 5 59
> 10322 5175808 8/5/2005 5 69
> 10322 5175888 8/17/2005 5 69
> 10322 5176031 8/8/2005 5 69
> 10322 5178039 8/17/2005 5 69
> 10322 5178905 8/23/2005 5 69
> 10322 5179328 8/23/2005 5 69
>
> So once it finishes doing 10321, it brings up the error, any idea what's
> wrong, thanks for all your help.
>
> Dejan.
> "Bernie Deitrick" wrote:
>
>> Dejan,
>>
>> > working, now it works fine,
>> Great!
>>
>> > thanks so much!
>> You're quite welcome.
>>
>> > Have a great day!
>> You, too.
>>
>> Bernie
>> MS Excel MVP
>>
>>
>> 


0
Bernie
9/30/2005 12:27:32 PM
Hello Again Bernie,

Thanks alot for your help again, I figured that out after some trial and 
error.

Now I have a nother problem, after it copies about 15 sheet or so I get this 
error:

Excel cannot complete this taks with available resources.  Choose less data 
or close other applications.

I push OK

then it says:

Run-Time error '1004':

PasteSpecial method of Range class failed

I push Debug

it highlights

mySht.Range("A1").PasteSpecial xlPasteValues

If i push End

it says:

The picture is too large and will be truncated.

I push OK

and it comes up two more times and the book closes.

What am I doing wrong now?  

Thanks for you input, once again.

btw: I have a 512 MB RAM, 2.0 GHZ Processor, Only Excel Open, I closed down 
all other prongrams.

Dejan

"Bernie Deitrick" wrote:

> Dejan,
> 
> Your key values are numbers, so change
> 
> myName = Worksheets(myCell.Value).Name
> 
> to
> 
> myName = Worksheets(CStr(myCell.Value)).Name
> 
> Worksheets can take either a number or a string - if it uses a number, it looks for that number 
> sheet (the 10321st sheet) rather than one with that sheet name.
> 
> Sorry about that - I wrote the base code to work with alpha-numeric keys.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Dejan" <Dejan@discussions.microsoft.com> wrote in message 
> news:573BA99E-92DD-4DD5-898C-0C2CB9E127BE@microsoft.com...
> > Hello Bernie!
> >
> > Sorry just one more problem, I was running the Macro on a actual sheet and I
> > got a this error:
> >
> > Run-time error '1004':
> >
> > Cannot rename a sheet to the same name as another sheet, a
> > referenced object library or workbook refreenced by Visual Basic.
> >
> > It creates the first sheet fine, when it gets to the second customer that is
> > when it screws up this is the macro:
> >
> > 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(Before:=Worksheets(1))
> > mySht.Name = myCell.Value
> > With myCell.CurrentRegion
> >    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
> >    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
> >      mySht.Range("A1").PasteSpecial xlPasteValues
> >      mySht.Range("A1").PasteSpecial xlPasteFormats
> >    mySht.Cells.EntireColumn.AutoFit
> >    .AutoFilter
> > End With
> > Resume
> > SheetExists:
> > Next myCell
> >
> >
> > End Sub
> >
> > Here are some of the values from the first few coloumns:
> >
> > A              B              C              D              E
> > ACCOUNT INVOICE INV DATE STR FBY
> > 10321 5173728 8/9/2005 5 59
> > 10321 5175563 8/4/2005 5 51
> > 10321 5175736 8/11/2005 5 69
> > 10321 5175804 8/5/2005 5 59
> > 10321 5176748 8/11/2005 5 59
> > 10321 5176751 8/11/2005 5 59
> > 10321 5178686 8/19/2005 5 69
> > 10321 5179033 8/25/2005 5 69
> > 10321 5179887 8/25/2005 5 69
> > 10322 3528294 8/19/2005 3 69
> > 10322 4347692 7/27/2005 4 69
> > 10322 4349443 8/8/2005 4 69
> > 10322 4352698 8/24/2005 4 69
> > 10322 5169595 7/26/2005 5 57
> > 10322 5173816 7/26/2005 5 57
> > 10322 5173834 7/26/2005 5 59
> > 10322 5173966 7/26/2005 5 59
> > 10322 5175808 8/5/2005 5 69
> > 10322 5175888 8/17/2005 5 69
> > 10322 5176031 8/8/2005 5 69
> > 10322 5178039 8/17/2005 5 69
> > 10322 5178905 8/23/2005 5 69
> > 10322 5179328 8/23/2005 5 69
> >
> > So once it finishes doing 10321, it brings up the error, any idea what's
> > wrong, thanks for all your help.
> >
> > Dejan.
> > "Bernie Deitrick" wrote:
> >
> >> Dejan,
> >>
> >> > working, now it works fine,
> >> Great!
> >>
> >> > thanks so much!
> >> You're quite welcome.
> >>
> >> > Have a great day!
> >> You, too.
> >>
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> 
> 
> 
> 
0
dejan (21)
9/30/2005 12:50:02 PM
Dejan,

Sounds like a memory-leak problem.  I haven't sued very large data sets with this macro: try adding

Application.CutCopyMode = False

just after the line:

..AutoFilter

Also, try this.  Put this at the top of your module (just below the option explicit statement)

Private Declare Function OpenClipboard Lib "user32" _
   (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

And put this somewhere in your module:

Sub ClearClipboard()
    OpenClipboard Application.hwnd
    EmptyClipboard
    CloseClipboard
End Sub

Then put the line

ClearClipboard

 within your loop (after the .AutoFilter line) as well.

HTH,
Bernie
MS Excel MVP


"Dejan" <Dejan@discussions.microsoft.com> wrote in message 
news:E2A0800A-544B-4591-AD59-9AA2E49AA77A@microsoft.com...
> Hello Again Bernie,
>
> Thanks alot for your help again, I figured that out after some trial and
> error.
>
> Now I have a nother problem, after it copies about 15 sheet or so I get this
> error:
>
> Excel cannot complete this taks with available resources.  Choose less data
> or close other applications.
>
> I push OK
>
> then it says:
>
> Run-Time error '1004':
>
> PasteSpecial method of Range class failed
>
> I push Debug
>
> it highlights
>
> mySht.Range("A1").PasteSpecial xlPasteValues
>
> If i push End
>
> it says:
>
> The picture is too large and will be truncated.
>
> I push OK
>
> and it comes up two more times and the book closes.
>
> What am I doing wrong now?
>
> Thanks for you input, once again.
>
> btw: I have a 512 MB RAM, 2.0 GHZ Processor, Only Excel Open, I closed down
> all other prongrams.
>
> Dejan
>
> "Bernie Deitrick" wrote:
>
>> Dejan,
>>
>> Your key values are numbers, so change
>>
>> myName = Worksheets(myCell.Value).Name
>>
>> to
>>
>> myName = Worksheets(CStr(myCell.Value)).Name
>>
>> Worksheets can take either a number or a string - if it uses a number, it looks for that number
>> sheet (the 10321st sheet) rather than one with that sheet name.
>>
>> Sorry about that - I wrote the base code to work with alpha-numeric keys.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Dejan" <Dejan@discussions.microsoft.com> wrote in message
>> news:573BA99E-92DD-4DD5-898C-0C2CB9E127BE@microsoft.com...
>> > Hello Bernie!
>> >
>> > Sorry just one more problem, I was running the Macro on a actual sheet and I
>> > got a this error:
>> >
>> > Run-time error '1004':
>> >
>> > Cannot rename a sheet to the same name as another sheet, a
>> > referenced object library or workbook refreenced by Visual Basic.
>> >
>> > It creates the first sheet fine, when it gets to the second customer that is
>> > when it screws up this is the macro:
>> >
>> > 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(Before:=Worksheets(1))
>> > mySht.Name = myCell.Value
>> > With myCell.CurrentRegion
>> >    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
>> >    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
>> >      mySht.Range("A1").PasteSpecial xlPasteValues
>> >      mySht.Range("A1").PasteSpecial xlPasteFormats
>> >    mySht.Cells.EntireColumn.AutoFit
>> >    .AutoFilter
>> > End With
>> > Resume
>> > SheetExists:
>> > Next myCell
>> >
>> >
>> > End Sub
>> >
>> > Here are some of the values from the first few coloumns:
>> >
>> > A              B              C              D              E
>> > ACCOUNT INVOICE INV DATE STR FBY
>> > 10321 5173728 8/9/2005 5 59
>> > 10321 5175563 8/4/2005 5 51
>> > 10321 5175736 8/11/2005 5 69
>> > 10321 5175804 8/5/2005 5 59
>> > 10321 5176748 8/11/2005 5 59
>> > 10321 5176751 8/11/2005 5 59
>> > 10321 5178686 8/19/2005 5 69
>> > 10321 5179033 8/25/2005 5 69
>> > 10321 5179887 8/25/2005 5 69
>> > 10322 3528294 8/19/2005 3 69
>> > 10322 4347692 7/27/2005 4 69
>> > 10322 4349443 8/8/2005 4 69
>> > 10322 4352698 8/24/2005 4 69
>> > 10322 5169595 7/26/2005 5 57
>> > 10322 5173816 7/26/2005 5 57
>> > 10322 5173834 7/26/2005 5 59
>> > 10322 5173966 7/26/2005 5 59
>> > 10322 5175808 8/5/2005 5 69
>> > 10322 5175888 8/17/2005 5 69
>> > 10322 5176031 8/8/2005 5 69
>> > 10322 5178039 8/17/2005 5 69
>> > 10322 5178905 8/23/2005 5 69
>> > 10322 5179328 8/23/2005 5 69
>> >
>> > So once it finishes doing 10321, it brings up the error, any idea what's
>> > wrong, thanks for all your help.
>> >
>> > Dejan.
>> > "Bernie Deitrick" wrote:
>> >
>> >> Dejan,
>> >>
>> >> > working, now it works fine,
>> >> Great!
>> >>
>> >> > thanks so much!
>> >> You're quite welcome.
>> >>
>> >> > Have a great day!
>> >> You, too.
>> >>
>> >> Bernie
>> >> MS Excel MVP
>> >>
>> >>
>> >>
>>
>>
>> 


0
Bernie
9/30/2005 1:18:38 PM
Hello Bernie,

I put the new code in and this is the error I get now, I think I put it in 
the right place tried to put in a few places but it doesn't want to work.

Also the ClearClipboard is not defined anywhere.

Thanks Bernie

Compile error:

Only comments may appear after End Sub, End Function, or end Property


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
Private Declare Function OpenClipboard Lib "user32" _
   (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

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(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
      mySht.Range("A1").PasteSpecial xlPasteValues
      mySht.Range("A1").PasteSpecial xlPasteFormats
    mySht.Cells.EntireColumn.AutoFit
    .AutoFilter
    Application.CutCopyMode = False
    ClearClipboard
End With
Resume
SheetExists:
Next myCell


End Sub

"Bernie Deitrick" wrote:

> Dejan,
> 
> Sounds like a memory-leak problem.  I haven't sued very large data sets with this macro: try adding
> 
> Application.CutCopyMode = False
> 
> just after the line:
> 
> ..AutoFilter
> 
> Also, try this.  Put this at the top of your module (just below the option explicit statement)
> 
> Private Declare Function OpenClipboard Lib "user32" _
>    (ByVal hwnd As Long) As Long
> Private Declare Function CloseClipboard Lib "user32" () As Long
> Private Declare Function EmptyClipboard Lib "user32" () As Long
> 
> And put this somewhere in your module:
> 
> Sub ClearClipboard()
>     OpenClipboard Application.hwnd
>     EmptyClipboard
>     CloseClipboard
> End Sub
> 
> Then put the line
> 
> ClearClipboard
> 
>  within your loop (after the .AutoFilter line) as well.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Dejan" <Dejan@discussions.microsoft.com> wrote in message 
> news:E2A0800A-544B-4591-AD59-9AA2E49AA77A@microsoft.com...
> > Hello Again Bernie,
> >
> > Thanks alot for your help again, I figured that out after some trial and
> > error.
> >
> > Now I have a nother problem, after it copies about 15 sheet or so I get this
> > error:
> >
> > Excel cannot complete this taks with available resources.  Choose less data
> > or close other applications.
> >
> > I push OK
> >
> > then it says:
> >
> > Run-Time error '1004':
> >
> > PasteSpecial method of Range class failed
> >
> > I push Debug
> >
> > it highlights
> >
> > mySht.Range("A1").PasteSpecial xlPasteValues
> >
> > If i push End
> >
> > it says:
> >
> > The picture is too large and will be truncated.
> >
> > I push OK
> >
> > and it comes up two more times and the book closes.
> >
> > What am I doing wrong now?
> >
> > Thanks for you input, once again.
> >
> > btw: I have a 512 MB RAM, 2.0 GHZ Processor, Only Excel Open, I closed down
> > all other prongrams.
> >
> > Dejan
> >
> > "Bernie Deitrick" wrote:
> >
> >> Dejan,
> >>
> >> Your key values are numbers, so change
> >>
> >> myName = Worksheets(myCell.Value).Name
> >>
> >> to
> >>
> >> myName = Worksheets(CStr(myCell.Value)).Name
> >>
> >> Worksheets can take either a number or a string - if it uses a number, it looks for that number
> >> sheet (the 10321st sheet) rather than one with that sheet name.
> >>
> >> Sorry about that - I wrote the base code to work with alpha-numeric keys.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Dejan" <Dejan@discussions.microsoft.com> wrote in message
> >> news:573BA99E-92DD-4DD5-898C-0C2CB9E127BE@microsoft.com...
> >> > Hello Bernie!
> >> >
> >> > Sorry just one more problem, I was running the Macro on a actual sheet and I
> >> > got a this error:
> >> >
> >> > Run-time error '1004':
> >> >
> >> > Cannot rename a sheet to the same name as another sheet, a
> >> > referenced object library or workbook refreenced by Visual Basic.
> >> >
> >> > It creates the first sheet fine, when it gets to the second customer that is
> >> > when it screws up this is the macro:
> >> >
> >> > 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(Before:=Worksheets(1))
> >> > mySht.Name = myCell.Value
> >> > With myCell.CurrentRegion
> >> >    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
> >> >    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
> >> >      mySht.Range("A1").PasteSpecial xlPasteValues
> >> >      mySht.Range("A1").PasteSpecial xlPasteFormats
> >> >    mySht.Cells.EntireColumn.AutoFit
> >> >    .AutoFilter
> >> > End With
> >> > Resume
> >> > SheetExists:
> >> > Next myCell
> >> >
> >> >
> >> > End Sub
> >> >
> >> > Here are some of the values from the first few coloumns:
> >> >
> >> > A              B              C              D              E
> >> > ACCOUNT INVOICE INV DATE STR FBY
> >> > 10321 5173728 8/9/2005 5 59
> >> > 10321 5175563 8/4/2005 5 51
> >> > 10321 5175736 8/11/2005 5 69
> >> > 10321 5175804 8/5/2005 5 59
> >> > 10321 5176748 8/11/2005 5 59
> >> > 10321 5176751 8/11/2005 5 59
> >> > 10321 5178686 8/19/2005 5 69
> >> > 10321 5179033 8/25/2005 5 69
> >> > 10321 5179887 8/25/2005 5 69
> >> > 10322 3528294 8/19/2005 3 69
> >> > 10322 4347692 7/27/2005 4 69
> >> > 10322 4349443 8/8/2005 4 69
> >> > 10322 4352698 8/24/2005 4 69
> >> > 10322 5169595 7/26/2005 5 57
> >> > 10322 5173816 7/26/2005 5 57
> >> > 10322 5173834 7/26/2005 5 59
> >> > 10322 5173966 7/26/2005 5 59
> >> > 10322 5175808 8/5/2005 5 69
> >> > 10322 5175888 8/17/2005 5 69
> >> > 10322 5176031 8/8/2005 5 69
> >> > 10322 5178039 8/17/2005 5 69
> >> > 10322 5178905 8/23/2005 5 69
> >> > 10322 5179328 8/23/2005 5 69
> >> >
> >> > So once it finishes doing 10321, it brings up the error, any idea what's
> >> > wrong, thanks for all your help.
> >> >
> >> > Dejan.
> >> > "Bernie Deitrick" wrote:
> >> >
> >> >> Dejan,
> >> >>
> >> >> > working, now it works fine,
> >> >> Great!
> >> >>
> >> >> > thanks so much!
> >> >> You're quite welcome.
> >> >>
> >> >> > Have a great day!
> >> >> You, too.
> >> >>
> >> >> Bernie
> >> >> MS Excel MVP
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
dejan (21)
9/30/2005 2:43:01 PM
Dejan,

Copy everything below into an otherwise blank codemodule.

HTH,
Bernie
MS Excel MVP

Option Explicit

Private Declare Function OpenClipboard Lib "user32" _
   (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

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(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
      mySht.Range("A1").PasteSpecial xlPasteValues
      mySht.Range("A1").PasteSpecial xlPasteFormats
    mySht.Cells.EntireColumn.AutoFit
    .AutoFilter
    Application.CutCopyMode = False
    ClearClipboard
End With
Resume
SheetExists:
Next myCell

End Sub

Sub ClearClipboard()
    OpenClipboard Application.hwnd
    EmptyClipboard
    CloseClipboard
End Sub





"Dejan" <Dejan@discussions.microsoft.com> wrote in message 
news:9E0B75D4-0A5B-4CB1-8E99-7240702ACA40@microsoft.com...
> Hello Bernie,
>
> I put the new code in and this is the error I get now, I think I put it in
> the right place tried to put in a few places but it doesn't want to work.
>
> Also the ClearClipboard is not defined anywhere.
>
> Thanks Bernie
>
> Compile error:
>
> Only comments may appear after End Sub, End Function, or end Property
>
>
> 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
> Private Declare Function OpenClipboard Lib "user32" _
>   (ByVal hwnd As Long) As Long
> Private Declare Function CloseClipboard Lib "user32" () As Long
> Private Declare Function EmptyClipboard Lib "user32" () As Long
>
> 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(Before:=Worksheets(1))
> mySht.Name = myCell.Value
> With myCell.CurrentRegion
>    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
>    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
>      mySht.Range("A1").PasteSpecial xlPasteValues
>      mySht.Range("A1").PasteSpecial xlPasteFormats
>    mySht.Cells.EntireColumn.AutoFit
>    .AutoFilter
>    Application.CutCopyMode = False
>    ClearClipboard
> End With
> Resume
> SheetExists:
> Next myCell
>
>
> End Sub
>
> "Bernie Deitrick" wrote:
>
>> Dejan,
>>
>> Sounds like a memory-leak problem.  I haven't sued very large data sets with this macro: try 
>> adding
>>
>> Application.CutCopyMode = False
>>
>> just after the line:
>>
>> ..AutoFilter
>>
>> Also, try this.  Put this at the top of your module (just below the option explicit statement)
>>
>> Private Declare Function OpenClipboard Lib "user32" _
>>    (ByVal hwnd As Long) As Long
>> Private Declare Function CloseClipboard Lib "user32" () As Long
>> Private Declare Function EmptyClipboard Lib "user32" () As Long
>>
>> And put this somewhere in your module:
>>
>> Sub ClearClipboard()
>>     OpenClipboard Application.hwnd
>>     EmptyClipboard
>>     CloseClipboard
>> End Sub
>>
>> Then put the line
>>
>> ClearClipboard
>>
>>  within your loop (after the .AutoFilter line) as well.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Dejan" <Dejan@discussions.microsoft.com> wrote in message
>> news:E2A0800A-544B-4591-AD59-9AA2E49AA77A@microsoft.com...
>> > Hello Again Bernie,
>> >
>> > Thanks alot for your help again, I figured that out after some trial and
>> > error.
>> >
>> > Now I have a nother problem, after it copies about 15 sheet or so I get this
>> > error:
>> >
>> > Excel cannot complete this taks with available resources.  Choose less data
>> > or close other applications.
>> >
>> > I push OK
>> >
>> > then it says:
>> >
>> > Run-Time error '1004':
>> >
>> > PasteSpecial method of Range class failed
>> >
>> > I push Debug
>> >
>> > it highlights
>> >
>> > mySht.Range("A1").PasteSpecial xlPasteValues
>> >
>> > If i push End
>> >
>> > it says:
>> >
>> > The picture is too large and will be truncated.
>> >
>> > I push OK
>> >
>> > and it comes up two more times and the book closes.
>> >
>> > What am I doing wrong now?
>> >
>> > Thanks for you input, once again.
>> >
>> > btw: I have a 512 MB RAM, 2.0 GHZ Processor, Only Excel Open, I closed down
>> > all other prongrams.
>> >
>> > Dejan
>> >
>> > "Bernie Deitrick" wrote:
>> >
>> >> Dejan,
>> >>
>> >> Your key values are numbers, so change
>> >>
>> >> myName = Worksheets(myCell.Value).Name
>> >>
>> >> to
>> >>
>> >> myName = Worksheets(CStr(myCell.Value)).Name
>> >>
>> >> Worksheets can take either a number or a string - if it uses a number, it looks for that 
>> >> number
>> >> sheet (the 10321st sheet) rather than one with that sheet name.
>> >>
>> >> Sorry about that - I wrote the base code to work with alpha-numeric keys.
>> >>
>> >> HTH,
>> >> Bernie
>> >> MS Excel MVP
>> >>
>> >>
>> >> "Dejan" <Dejan@discussions.microsoft.com> wrote in message
>> >> news:573BA99E-92DD-4DD5-898C-0C2CB9E127BE@microsoft.com...
>> >> > Hello Bernie!
>> >> >
>> >> > Sorry just one more problem, I was running the Macro on a actual sheet and I
>> >> > got a this error:
>> >> >
>> >> > Run-time error '1004':
>> >> >
>> >> > Cannot rename a sheet to the same name as another sheet, a
>> >> > referenced object library or workbook refreenced by Visual Basic.
>> >> >
>> >> > It creates the first sheet fine, when it gets to the second customer that is
>> >> > when it screws up this is the macro:
>> >> >
>> >> > 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(Before:=Worksheets(1))
>> >> > mySht.Name = myCell.Value
>> >> > With myCell.CurrentRegion
>> >> >    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
>> >> >    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
>> >> >      mySht.Range("A1").PasteSpecial xlPasteValues
>> >> >      mySht.Range("A1").PasteSpecial xlPasteFormats
>> >> >    mySht.Cells.EntireColumn.AutoFit
>> >> >    .AutoFilter
>> >> > End With
>> >> > Resume
>> >> > SheetExists:
>> >> > Next myCell
>> >> >
>> >> >
>> >> > End Sub
>> >> >
>> >> > Here are some of the values from the first few coloumns:
>> >> >
>> >> > A              B              C              D              E
>> >> > ACCOUNT INVOICE INV DATE STR FBY
>> >> > 10321 5173728 8/9/2005 5 59
>> >> > 10321 5175563 8/4/2005 5 51
>> >> > 10321 5175736 8/11/2005 5 69
>> >> > 10321 5175804 8/5/2005 5 59
>> >> > 10321 5176748 8/11/2005 5 59
>> >> > 10321 5176751 8/11/2005 5 59
>> >> > 10321 5178686 8/19/2005 5 69
>> >> > 10321 5179033 8/25/2005 5 69
>> >> > 10321 5179887 8/25/2005 5 69
>> >> > 10322 3528294 8/19/2005 3 69
>> >> > 10322 4347692 7/27/2005 4 69
>> >> > 10322 4349443 8/8/2005 4 69
>> >> > 10322 4352698 8/24/2005 4 69
>> >> > 10322 5169595 7/26/2005 5 57
>> >> > 10322 5173816 7/26/2005 5 57
>> >> > 10322 5173834 7/26/2005 5 59
>> >> > 10322 5173966 7/26/2005 5 59
>> >> > 10322 5175808 8/5/2005 5 69
>> >> > 10322 5175888 8/17/2005 5 69
>> >> > 10322 5176031 8/8/2005 5 69
>> >> > 10322 5178039 8/17/2005 5 69
>> >> > 10322 5178905 8/23/2005 5 69
>> >> > 10322 5179328 8/23/2005 5 69
>> >> >
>> >> > So once it finishes doing 10321, it brings up the error, any idea what's
>> >> > wrong, thanks for all your help.
>> >> >
>> >> > Dejan.
>> >> > "Bernie Deitrick" wrote:
>> >> >
>> >> >> Dejan,
>> >> >>
>> >> >> > working, now it works fine,
>> >> >> Great!
>> >> >>
>> >> >> > thanks so much!
>> >> >> You're quite welcome.
>> >> >>
>> >> >> > Have a great day!
>> >> >> You, too.
>> >> >>
>> >> >> Bernie
>> >> >> MS Excel MVP
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Bernie
9/30/2005 2:53:18 PM
Hello,

Sorry to bother again, did as  you told, it did run a little longer but 
still the same problem....

So i guess I'm back to square one then.

I really appreciate you tyring.

Dejan

"Bernie Deitrick" wrote:

> Dejan,
> 
> Copy everything below into an otherwise blank codemodule.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> Option Explicit
> 
> Private Declare Function OpenClipboard Lib "user32" _
>    (ByVal hwnd As Long) As Long
> Private Declare Function CloseClipboard Lib "user32" () As Long
> Private Declare Function EmptyClipboard Lib "user32" () As Long
> 
> 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(Before:=Worksheets(1))
> mySht.Name = myCell.Value
> With myCell.CurrentRegion
>     .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
>     myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
>       mySht.Range("A1").PasteSpecial xlPasteValues
>       mySht.Range("A1").PasteSpecial xlPasteFormats
>     mySht.Cells.EntireColumn.AutoFit
>     .AutoFilter
>     Application.CutCopyMode = False
>     ClearClipboard
> End With
> Resume
> SheetExists:
> Next myCell
> 
> End Sub
> 
> Sub ClearClipboard()
>     OpenClipboard Application.hwnd
>     EmptyClipboard
>     CloseClipboard
> End Sub
> 
> 
> 
> 
> 
> "Dejan" <Dejan@discussions.microsoft.com> wrote in message 
> news:9E0B75D4-0A5B-4CB1-8E99-7240702ACA40@microsoft.com...
> > Hello Bernie,
> >
> > I put the new code in and this is the error I get now, I think I put it in
> > the right place tried to put in a few places but it doesn't want to work.
> >
> > Also the ClearClipboard is not defined anywhere.
> >
> > Thanks Bernie
> >
> > Compile error:
> >
> > Only comments may appear after End Sub, End Function, or end Property
> >
> >
> > 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
> > Private Declare Function OpenClipboard Lib "user32" _
> >   (ByVal hwnd As Long) As Long
> > Private Declare Function CloseClipboard Lib "user32" () As Long
> > Private Declare Function EmptyClipboard Lib "user32" () As Long
> >
> > 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(Before:=Worksheets(1))
> > mySht.Name = myCell.Value
> > With myCell.CurrentRegion
> >    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
> >    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
> >      mySht.Range("A1").PasteSpecial xlPasteValues
> >      mySht.Range("A1").PasteSpecial xlPasteFormats
> >    mySht.Cells.EntireColumn.AutoFit
> >    .AutoFilter
> >    Application.CutCopyMode = False
> >    ClearClipboard
> > End With
> > Resume
> > SheetExists:
> > Next myCell
> >
> >
> > End Sub
> >
> > "Bernie Deitrick" wrote:
> >
> >> Dejan,
> >>
> >> Sounds like a memory-leak problem.  I haven't sued very large data sets with this macro: try 
> >> adding
> >>
> >> Application.CutCopyMode = False
> >>
> >> just after the line:
> >>
> >> ..AutoFilter
> >>
> >> Also, try this.  Put this at the top of your module (just below the option explicit statement)
> >>
> >> Private Declare Function OpenClipboard Lib "user32" _
> >>    (ByVal hwnd As Long) As Long
> >> Private Declare Function CloseClipboard Lib "user32" () As Long
> >> Private Declare Function EmptyClipboard Lib "user32" () As Long
> >>
> >> And put this somewhere in your module:
> >>
> >> Sub ClearClipboard()
> >>     OpenClipboard Application.hwnd
> >>     EmptyClipboard
> >>     CloseClipboard
> >> End Sub
> >>
> >> Then put the line
> >>
> >> ClearClipboard
> >>
> >>  within your loop (after the .AutoFilter line) as well.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Dejan" <Dejan@discussions.microsoft.com> wrote in message
> >> news:E2A0800A-544B-4591-AD59-9AA2E49AA77A@microsoft.com...
> >> > Hello Again Bernie,
> >> >
> >> > Thanks alot for your help again, I figured that out after some trial and
> >> > error.
> >> >
> >> > Now I have a nother problem, after it copies about 15 sheet or so I get this
> >> > error:
> >> >
> >> > Excel cannot complete this taks with available resources.  Choose less data
> >> > or close other applications.
> >> >
> >> > I push OK
> >> >
> >> > then it says:
> >> >
> >> > Run-Time error '1004':
> >> >
> >> > PasteSpecial method of Range class failed
> >> >
> >> > I push Debug
> >> >
> >> > it highlights
> >> >
> >> > mySht.Range("A1").PasteSpecial xlPasteValues
> >> >
> >> > If i push End
> >> >
> >> > it says:
> >> >
> >> > The picture is too large and will be truncated.
> >> >
> >> > I push OK
> >> >
> >> > and it comes up two more times and the book closes.
> >> >
> >> > What am I doing wrong now?
> >> >
> >> > Thanks for you input, once again.
> >> >
> >> > btw: I have a 512 MB RAM, 2.0 GHZ Processor, Only Excel Open, I closed down
> >> > all other prongrams.
> >> >
> >> > Dejan
> >> >
> >> > "Bernie Deitrick" wrote:
> >> >
> >> >> Dejan,
> >> >>
> >> >> Your key values are numbers, so change
> >> >>
> >> >> myName = Worksheets(myCell.Value).Name
> >> >>
> >> >> to
> >> >>
> >> >> myName = Worksheets(CStr(myCell.Value)).Name
> >> >>
> >> >> Worksheets can take either a number or a string - if it uses a number, it looks for that 
> >> >> number
> >> >> sheet (the 10321st sheet) rather than one with that sheet name.
> >> >>
> >> >> Sorry about that - I wrote the base code to work with alpha-numeric keys.
> >> >>
> >> >> HTH,
> >> >> Bernie
> >> >> MS Excel MVP
> >> >>
> >> >>
> >> >> "Dejan" <Dejan@discussions.microsoft.com> wrote in message
> >> >> news:573BA99E-92DD-4DD5-898C-0C2CB9E127BE@microsoft.com...
> >> >> > Hello Bernie!
> >> >> >
> >> >> > Sorry just one more problem, I was running the Macro on a actual sheet and I
> >> >> > got a this error:
> >> >> >
> >> >> > Run-time error '1004':
> >> >> >
> >> >> > Cannot rename a sheet to the same name as another sheet, a
> >> >> > referenced object library or workbook refreenced by Visual Basic.
> >> >> >
> >> >> > It creates the first sheet fine, when it gets to the second customer that is
> >> >> > when it screws up this is the macro:
> >> >> >
> >> >> > 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(Before:=Worksheets(1))
> >> >> > mySht.Name = myCell.Value
> >> >> > With myCell.CurrentRegion
> >> >> >    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
> >> >> >    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
> >> >> >      mySht.Range("A1").PasteSpecial xlPasteValues
> >> >> >      mySht.Range("A1").PasteSpecial xlPasteFormats
> >> >> >    mySht.Cells.EntireColumn.AutoFit
> >> >> >    .AutoFilter
> >> >> > End With
> >> >> > Resume
> >> >> > SheetExists:
> >> >> > Next myCell
> >> >> >
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >> > Here are some of the values from the first few coloumns:
> >> >> >
> >> >> > A              B              C              D              E
> >> >> > ACCOUNT INVOICE INV DATE STR FBY
> >> >> > 10321 5173728 8/9/2005 5 59
> >> >> > 10321 5175563 8/4/2005 5 51
> >> >> > 10321 5175736 8/11/2005 5 69
> >> >> > 10321 5175804 8/5/2005 5 59
> >> >> > 10321 5176748 8/11/2005 5 59
> >> >> > 10321 5176751 8/11/2005 5 59
> >> >> > 10321 5178686 8/19/2005 5 69
0
dejan (21)
9/30/2005 3:10:04 PM
Dejan,

What happens if you stop the macro and then restart it? Try this, which will only do a set number of 
sheets (20) each time it is run.

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
Dim Counter As Integer

Counter = 0
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(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
    .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
    myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
      mySht.Range("A1").PasteSpecial xlPasteValues
      mySht.Range("A1").PasteSpecial xlPasteFormats
    mySht.Cells.EntireColumn.AutoFit
    .AutoFilter
    Application.CutCopyMode = False
    ClearClipboard
End With
Counter = Counter +1
If Counter = 20 Then Exit Sub
Resume
SheetExists:
Next myCell

End Sub



"Dejan" <Dejan@discussions.microsoft.com> wrote in message 
news:40A78201-E773-4B7E-9899-300F0AD66C1A@microsoft.com...
> Hello,
>
> Sorry to bother again, did as  you told, it did run a little longer but
> still the same problem....
>
> So i guess I'm back to square one then.
>
> I really appreciate you tyring.
>
> Dejan


0
Bernie
9/30/2005 3:19:27 PM
Hello,

Yes that did work, not bad, still going to save alot of time.

Thanks and if you ever come up with something else, let me know.

Thanks.

Have a good weekend.

Dejan

"Bernie Deitrick" wrote:

> Dejan,
> 
> What happens if you stop the macro and then restart it? Try this, which will only do a set number of 
> sheets (20) each time it is run.
> 
> 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
> Dim Counter As Integer
> 
> Counter = 0
> 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(Before:=Worksheets(1))
> mySht.Name = myCell.Value
> With myCell.CurrentRegion
>     .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
>     myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
>       mySht.Range("A1").PasteSpecial xlPasteValues
>       mySht.Range("A1").PasteSpecial xlPasteFormats
>     mySht.Cells.EntireColumn.AutoFit
>     .AutoFilter
>     Application.CutCopyMode = False
>     ClearClipboard
> End With
> Counter = Counter +1
> If Counter = 20 Then Exit Sub
> Resume
> SheetExists:
> Next myCell
> 
> End Sub
> 
> 
> 
> "Dejan" <Dejan@discussions.microsoft.com> wrote in message 
> news:40A78201-E773-4B7E-9899-300F0AD66C1A@microsoft.com...
> > Hello,
> >
> > Sorry to bother again, did as  you told, it did run a little longer but
> > still the same problem....
> >
> > So i guess I'm back to square one then.
> >
> > I really appreciate you tyring.
> >
> > Dejan
> 
> 
> 
0
dejan (21)
9/30/2005 3:32:05 PM
Dejan,

Great! And we've shattered my record for the greatest number of posts in a single thread!  ;-)

Bernie
MS Excel MVP


"Dejan" <Dejan@discussions.microsoft.com> wrote in message 
news:12942A4D-55A0-4246-9D92-1BBAD98A7BA2@microsoft.com...
> Hello,
>
> Yes that did work, not bad, still going to save alot of time.
>
> Thanks and if you ever come up with something else, let me know.
>
> Thanks.
>
> Have a good weekend.
>
> Dejan


0
Bernie
9/30/2005 3:44:20 PM
Hello Bernie,

Glad I could help you sir!  Here is another one to add, I think I'm going to 
do some research on google and see if I can find any other code to clear the 
clipboard aside from the one you gave me.

I will post if I find anything better.

Thanks Again!

Dejan

"Bernie Deitrick" wrote:

> Dejan,
> 
> Great! And we've shattered my record for the greatest number of posts in a single thread!  ;-)
> 
> Bernie
> MS Excel MVP
> 
> 
> "Dejan" <Dejan@discussions.microsoft.com> wrote in message 
> news:12942A4D-55A0-4246-9D92-1BBAD98A7BA2@microsoft.com...
> > Hello,
> >
> > Yes that did work, not bad, still going to save alot of time.
> >
> > Thanks and if you ever come up with something else, let me know.
> >
> > Thanks.
> >
> > Have a good weekend.
> >
> > Dejan
> 
> 
> 
0
dejan (21)
9/30/2005 3:58:03 PM
Hello Bernie,

Once again, I need your help sir!  I found a really good macro, maybe you 
can use this guy for the future as well. It does an awesome job, I just have 
one problem.  I need it to be able to copy a subtotal line at the bottom of 
the data table from sheet one to each worksheet, I tried putting something 
in, but it's not working out, you can have look, it's commented, this is the 
macro I ended up using from you.  Also I added a print formating Sub, not 
sure If this is wrong or an easier way of doing this.

Thanks so much for your help.

Sub Copy_With_AdvancedFilter_To_Worksheets()
    Dim CalcMode As Long
    Dim ws1 As Worksheet
    Dim WSNew As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim Lrow As Long
    
    
    
    Set ws1 = Sheets("Sheet1")  '<<< Change
    
    'Set ws1 = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

    'Set ws1 = myArea.Resize(myArea.Rows.Count - 1, 1)
    'Tip : Use a Dynamic range name, 
http://www.contextures.com/xlNames01.html#Dynamic
    'or a fixed range like Range("A1:H1200")
    Set rng = ws1.Range("A1").CurrentRegion  '<<< Change
 
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    With ws1
        rng.Columns(1).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=.Range("IV1"), Unique:=True
        'This example filter on the first column in the range (change this 
if needed)
        'You see that the last two columns of the worksheet are used to make 
a Unique list
        'and add the CriteriaRange.(you can't use this macro if you use the 
columns)

        Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
        .Range("IU1").Value = .Range("IV1").Value
 
        For Each cell In .Range("IV2:IV" & Lrow)
            .Range("IU2").Value = cell.Value
            Set WSNew = Sheets.Add
            Printing
            On Error Resume Next
            WSNew.Name = cell.Value
            If Err.Number > 0 Then
                MsgBox "Change the name of : " & WSNew.Name & " manually"
                Err.CLEAR
            End If
            On Error GoTo 0
            rng.AdvancedFilter Action:=xlFilterCopy, _
                               CriteriaRange:=.Range("IU1:IU2"), _
                               CopyToRange:=WSNew.Range("A1"), _
                               Unique:=False
            WSNew.Columns.AutoFit
        Next
        .Columns("IU:IV").CLEAR
    End With
 
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

Sub Printing()
'
' Printing Macro
' Macro recorded 10/3/2005 by Dejan Lukic
'

'
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&F"
        .CenterFooter = "&A"
        .RightFooter = "&P OF &N"
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub


0
dejan (21)
10/6/2005 1:01:03 PM
Reply:

Similar Artilces:

Auto Filter problem
I am using the auto filter for a particular column to sort out differen medical programs. When I click the drop down and click a program, i doesnt show me ALL the lines that say this particular program. Why is this? -- Message posted from http://www.ExcelForum.com Hi maybe some hidden characters in these lines 8e.g. additional space characters, etc.) -- Regards Frank Kabel Frankfurt, Germany "jkb724 >" <<jkb724.19rasx@excelforum-nospam.com> schrieb im Newsbeitrag news:jkb724.19rasx@excelforum-nospam.com... > I am using the auto filter for a particular column to so...

My keying is changed ...
Hi, When I enter text into Outlook 2000 it shows different results. For example, if I enter dog it may show ket. Every character is changed. If I close Outlook and reopen Outlook everything is working fine for a while then it starts up again. Today, a person who sends me email and I send email to has the same problem. Did a virus cause this? If so, how do I get rid of the problem. Also, how did this happen if I have antivirus protection and a firewall? Thank you for your help. On 12 Apr 2005 15:57:02 -0700, lanalewis@yahoo.com wrote: >Hi, >When I enter text into Outlook 2000 i...

custom report filter by customer & supplier
We would like to have the reporting capability to pull a customer list with name, address, email and phone number (where available of course) filtered on customers who have purchased Vera Bradley(Supplier) products in the past. We do use Vera Bradley as a department. The output would be, for example, 1000 customers out of 4000 in the total customer database have purchased a Vera Bradley product and thus show Vera Bradley in their detailed customer purchases. The report would give us just the subset of 1000 customers to use for target marketing purposes. The report would need to be...

Filter and Page Setup
Hi All, I have a question about filters and page headings for printing a filtered document. I basically need advice on the best way to accomplish the below listed task. I have set up a spreadsheet for logging transmittals received from different offices that my company has around the world. In this spreadsheet, I have numerous rows of information that we need to keep track of. I would like to place filters on SOME of the columns (not all of them). When I have the filter on, and I print out the document, I would like it to have a title that is relevant to the filter. For example, ...

Project Accounting Name field change
I am in GP 10 on the Contract Maintenance Screen. I want to change the prompt 'Name' to Contract Number. When I go into Modifier->Resources->Strings there is no field called Name. How can I change this prompt without having to use Forms Modifier to change the prompt? I was able to change several fields with no problem. First of all, you need to make sure you are in the proper dictionary -- Field Service. By the way, there is no need to add the form to Modifier to modify the string. If you want to change the prompt programmatically, you will need to use VBA. Ad...

Auto-Editing
Word 2003 has a very annoying default. It assumes too much! If I reformat a few words in a document, it changes the entire document to that formatting. Grr-rrr-r! I have to constantly press undo to get what I want. I know there's a feature that's causing this annoyance but I can't find it to turn it off! Can anyone help, please? See http://word.mvps.org/faqs/formatting/wholedocumentreformatted.htm. -- Stefan Blom Microsoft Word MVP "Connie Martin" <ConnieMartin@discussions.microsoft.com> wrote in message news:CD959D82-F81B-4A9D-993E-73...

How do I change the "Project Type" in "law Firm Financial Analys..
he "Lawfirm Financial Analysis Worksheet" currently has only attorney related "Projects Types," e.g., bankruptcy, forming corporations, amublence chasing. I'd like to modify the worksheet so it would fit other professions, i.e., engineering, consulting, and the like. Are these worksheets, including the law firm financial analysis worksheet, modifyable? If you download the worksheet: http://office.microsoft.com/en-ca/templates/TC011406201033.aspx you should be able to modify it. Clear the existing project types, and replace them with the ones you need. seanac...

Auto-Calculate
I have a spreadsheet that utilize the stock quote add-in from MSN. On a daily basis I refresh the add-in functions for the Highs, Lows, Last, Volume, etc. On occasion the spreadsheet reverts to manual caculation settings, and I have to press the F9 even after I updated the quotes. I've always preferred the any spreadsheets I open, blank or otherwise, the automatic setting selected for calculations. I consider this an annoyance. Has anyone experienced this glitch? Is there a fix for it? Ron_D Ron Excel takes the calculation mode from the first workbook that opens in a session. Sub...

Set minimum values for equation results
Hi all, I've been searching the queries here and can't find an answer to what I'm hoping is a simple query . . . I want my equation to ensure that any minus values are returned as 0 but positive values should remain true. THis needs to be within the original equation as opposed to a separate equation doing a find and replace. Appreciate any answers (preferably by e-mail!). Jim. Try using IF(), for example: Put in C1: =IF((A1-B1)<0,0,A1-B1) which will return 0 if (A1-B1) results in a minus value with zero & positive values remaining true Hope the above helps. M...

auto copy self
How do I arrange for Outlook to automatically copy myself on every new, reply or forward email? If you mean keep a copy of all sent, see settings under Tools/Options/Email Options and Advanced Options David "rich" <steamuk@aol.com> wrote in message news:055c01c3664f$dadecd30$a001280a@phx.gbl... > How do I arrange for Outlook to automatically copy myself > on every new, reply or forward email? ...

Auto correct or Auto complete
We are all familiar witht he auto correct or auto complete functions found in MS applications. Of course that means it is available in Access also. My question is this ... can this function be replicated within the DB, so that the set of auto correct items are part of the package rather than something that would have to be set up on each individual desktop system? It's use, for me, would primarily be within a memo field where medical abreviations, used as a shortcut, would convert to the true meaning as the user types them in. Ex: "prn" without quotes would change to. &qu...

Auto Scale Chart Macro -- Any improvements please?
This is the best way I've found to correctly scale a chart -- the only way I found to exclude a chart interpolating with #NA cells was to delete those cells containing #NA, and run the chart. This takes a long time on my computer. Wondering if there is a better way. This macro is directly taken from 2 excel mvps and modified; i believe a mehta and ron bovarty, but could be wrong. Thanks for taking a look. Sub AutoScaleYAxes() Dim ValuesArray(), SeriesValues As Variant Dim Ctr As Integer, TotCtr As Integer Application.Run "Extend_Stock_Data" Applic...

Column reference got changed
Column references in my excel sheets which which usually show as A B C D ....... are now showing 1 2 3 4 5 Please help willy wrote: >Column references in my excel sheets which which usually show as A B C D >...... are now showing 1 2 3 4 5 >Please help Tools | Options, General tab. Untick R1C1 reference style. -- Message posted via http://www.officekb.com Goto <Tools><Options><Settings> and untick <R1C1 reference style>. Regards. Bill Ridgeway Computer Solutions "willy" <willy@discussions.microsoft.com> wrote in message news:DB0C70...

OWA auto login
I am trying to use the credentials from the forms authentication login to automaticaly log in to Outlook Web. I redirect to a URL with this format: http://UserName:Password@mailserver/exchange I have seen this example in other posts but this does not work for me. I still get the Outlook Windows Login prompt. Perry Perecli Manole wrote: > I am trying to use the credentials from the forms authentication > login to automaticaly log in to Outlook Web. I redirect to a URL with > this format: http://UserName:Password@mailserver/exchange > I have seen this example in other posts but t...

unsaved changes
Hey all, I was just curious does anyone know if you can recover an unsaved data, i made changes to an excel worksheet and I accidently exited out without saving the changes, can i recover all that data, it took me an hour to do? If you exited XL normally, you're out of luck. In article <BC3B2D1C-31C6-479A-A53D-1DF8190F67A2@microsoft.com>, accidentalprone <accidentalprone@discussions.microsoft.com> wrote: > Hey all, > > I was just curious does anyone know if you can recover an unsaved data, i > made changes to an excel worksheet and I accidently exited out...

Any way to get rid of an auto-suggestion in the login dialog box, for Exchange/OL using RPC over HTTP?
A client was using RPC over HTTP to two different Exchange servers (OL prompting for profile on startup). Both profiles were set up to prompt for credentials upon connection to Exchange. There's now only one Outlook profile needed; the other is gone and OL isn't prompting for the profile selection any longer, which is fine. However, Outlook is remembering domain1\user and domain2\user in the login dialog box, and I'd love to be able to get rid of the no-longer-valid credential dropdown suggestion. (The computer is not a member of a domain and hence always needs to be prom...

Turning off formatting when tracking changes
When we are tracking changes, then save the document, and open it later, the formatting shows up on the right side of the page. When I click on the Review tab and unclick the arrow beside Formatting under show Mark-up, it goes away. Then I save, and every time I open it, it's back again. I only want to track the changes in the document, not the formatting itself, but it seems as if this is a default and I can NOT get it to go away....I don't want to accept the changes until appropriate folks have reviewed it, but when I send them the document, they all complain about t...

merge results of many records on one page
I want to merge many records as consecutive lines on a page as opposed to each page representing a single record. Thus turning Publisher into a report writing tool. I have no difficulty merging labels and standard type letters etc using mail merge from Access or Excel as the data source. Setup your page the same way you would labels. If you want 30 records per page with a ..5 margin all around, divide 10 inches by 30, setup your custom page, 7.5 x 0.34. With a text box this narrow you will have to change the font size and set the text box top and bottom margin to zero. Do some experime...

Auto look through subfolders
Please help. I can write a macro that will extract data from all excel files in a specific folder. What I need to be able to do is write a macro that will automatically extract data from excel files within folders and sub folders and sub sub folders etc For example the data is all held on the following directory: H:\development forms\2006 cost sheets\ And within that directory by customer - for example Tesco, Boots, Asda etc. These customers are then subdivided into product ranges - eg 1,2,3,4 etc and some futher subdivided by national or exclusive. All the excel files are in exactly th...

Macro to turn on "Track Changes" function
I'm trying to create a macro that turns on the "Track Changes" function with a shortcut key. The problem is that while I'm recording the macro the traditional way, the recording process stops when the "Track Changes" function is turned on. Is there a way to overcome this problem or is the "Track Changes" function not possible to put into a macro, even in Visual Basic? ...

Counting Records after filtering by feilds
I am working on a project that will create a report to count the number of open work orders (records that have their opendate field with in a date range given), close work orders (records that have their closeddate field with in the same date range given earlier) and pending work orders (records that do not have an entry in the closed date field). I don't know how to (a) prompt the user for a given date range, im thinking of maybe using a form interface? Is it possible for me to have a calander button to click to select a date from there? and (b) To count the what was previously describe...

An auto reply to an auto reply!
Had the funniest situation yesterday. Somebody emailed a public folder we have set up. On there we have a rule to send a reply saying thanks for your email -someone will be with you shortly (we have to have this message on - it is for some website that recently went live). Now that auto reply went back to the guy who emailed - who must have sent it from a public folder himself (as he forwarded the original email from a public folder). The guy received our auto reply - and promptly his email system sent an auto reply to us. As is the way it is set up - our email system sent him an auto ...

NDRs, Outlook contacts, GAL changes
Hi, We recently allowed a staff member access to update people's email addresses and display names in our Global Address list on an Exchange 2000 (Windows 2000) server. the GAL has about 20,000 mostly external address entries. (There are only 30 staff in our organization.) For display consistency, this staff person has changed many display names in the GAL to a standardized format, i.e. Lastname Firstname -Company. Prior to that, it may or may not have displayed the outside person's organization. Also, some of the listings in the GAL had people's employee numbers as their alias b...

Changes are not saved on files
Hi, Sometimes when files are closed after beeing saved and then the user open this file, the last changes are not present (like user didn't save it). This happens on netwerk drive but also on local drive. We have also an antivirus but the searches on the antivirus support don't give anything. We experience that on windows2000 machines with OfficeXP and with WindowsXP machines with office2003. Any ideas are welcome. Thanks I would suspect that the user isn't opening the file that he/she saved. This used to happen a lot with people who would open email attachments, make changes...

Auto date
I am trying to create a field in a form that if anything is changed on it, it will automatically update the date to current date. Is this possible? Sojaminc wrote: >I am trying to create a field in a form that if anything is changed on it, it >will automatically update the date to current date. Is this possible? Make sure the last changed date/time field is in the form's record source table/query. Then use the form's BeforeUpdate event: Me.lastchanged = Now -- Marsh MVP [MS Access] Hi - You can do this in the On Dirty event of the form, which fires as soon as you m...