I need some help modifying this macro. I found this in a post by
Bernie Deitrick:


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(0,
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


'Optional section to export the sheets to separate files
'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

And it does almost what I want it to do. Unfortunately I have 2
worksheets and when this divides the data out it overwrites everything
from the first worksheet. I have tried to find some Append macros to
combine with this but I am not very proficient in visual basic.
Anyone out there know have some ideas?

Thank you for the help,

Dow.

To get meaningful assistance, you need to clearly state what your objective
is. We can see what the macro does, what do you want it to do.

"Dow" wrote:

> I need some help modifying this macro. I found this in a post by
> Bernie Deitrick:
>
>
> 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(0,
> 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
>
>
> 'Optional section to export the sheets to separate files
> '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
>
> And it does almost what I want it to do. Unfortunately I have 2
> worksheets and when this divides the data out it overwrites everything
> from the first worksheet. I have tried to find some Append macros to
> combine with this but I am not very proficient in visual basic.
> Anyone out there know have some ideas?
>
> Thank you for the help,
>
> Dow.
>

The bad news is that you're looping through the cells in that column. And if
there are duplicates, you're processing that data more than once.

Instead of modifying the code you have, you may want to look here:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dow wrote:
>
> I need some help modifying this macro. I found this in a post by
> Bernie Deitrick:
>
> 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(0,
> 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
>
> 'Optional section to export the sheets to separate files
> '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
>
> And it does almost what I want it to do. Unfortunately I have 2
> worksheets and when this divides the data out it overwrites everything
> from the first worksheet. I have tried to find some Append macros to
> combine with this but I am not very proficient in visual basic.
> Anyone out there know have some ideas?
>
> Thank you for the help,
>
> Dow.

--

Dave Peterson

On Mar 7, 6:50*pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> To get meaningful assistance, you need to clearly state what your objective
> is. *We can see what the macro does, what do you want it to do.
>
>
>
> "Dow" wrote:
> > I need some help modifying this macro. *I found this in a post by
> > Bernie Deitrick:
>
> > 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(0,
> > 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
>
> > 'Optional section to export the sheets to separate files
> > '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
>
> > And it does almost what I want it to do. *Unfortunately I have 2
> > worksheets and when this divides the data out it overwrites everything
> > from the first worksheet. *I have tried to find some Append macros to
> > combine with this but I am not very proficient in visual basic.
> > Anyone out there know have some ideas?
>
> > Thank you for the help,
>
> > Dow.- Hide quoted text -
>
> - Show quoted text -

Sorry that was not clear. I would like it to append the information.
So it runs through sheet one and splits the data into the new
worksheets, then it runs through the second sheet and appends that
data onto the same new worksheets. This macro overwrites the data so
that on the new worksheets I only see data from the second sheet and
nothing from the first. There are no duplicates in the data I am
using.

Hopefully this longer post helps explain the trouble I am having.
Thank you for your help.

The data from a report I run is large enough that it has to be put on
2 seperate worksheets each month, the tab names differ from month to
month.

I am looking for a macro that will look at a specific column in each
worksheet and split the data out into seperate tabs. For example the
values in Column K on each worksheet can be one of 5 (or more) values
A, B, C, D, or E. I want all the rows with A copied onto a seperate
sheet, all the rows with B's copied onto another sheet, etc.

The code I found by Bernie Deitrick almost works. There are some
issues I am running into.

Sometimes I get a "Run-time error '1004' Excel cannot create or use
the data range reference because it is too complex". When I go to
debug this is highlighted:

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

When I put the curser over this it shows "xlCelltypeVisible=12"

It does not always do this. Even in the same month if I rerun the
data, following each steps exactly as I did before. I hope someone
can tell me why this is.

The big issue I am having is that it does not leave all the data.

I run the macro and it seems to go through both worksheets. When I go
to the created worksheets there are only enough rows to account for
one sheet. I believe it is overwriting the data from the first sheet
with the data from the second sheet.

Does anyone have any ideas how to append the data so that I get all of
the information from both sheets?

On Mar 11, 10:10*am, Dow <dfairbank...@hotmail.com> wrote:
> Hopefully this longer post helps explain the trouble I am having.
> Thank you for your help.
>
> The data from a report I run is large enough that it has to be put on
> 2 seperate worksheets each month, the tab names differ from month to
> month.
>
> I am looking for a macro that will look at a specific column in each
> worksheet and split the data out into seperate tabs. *For example the
> values in Column K on each worksheet can be one of 5 (or more) values
> A, B, C, D, or E. *I want all the rows with A copied onto a seperate
> sheet, all the rows with B's copied onto another sheet, etc.
>
> The code I found by Bernie Deitrick almost works. *There are some
> issues I am running into.
>
> Sometimes I get a "Run-time error '1004' Excel cannot create or use
> the data range reference because it is too complex". *When I go to
> debug this is highlighted:
>
> .SpecialCells(xlCellTypeVisible).Copy _
> * * * * mySht.Range("A1")
>
> When I put the curser over this it shows "xlCelltypeVisible=12"
>
> It does not always do this. *Even in the same month if I rerun the
> data, following each steps exactly as I did before. *I hope someone
> can tell me why this is.
>
> The big issue I am having is that it does not leave all the data.
>
> I run the macro and it seems to go through both worksheets. *When I go
> to the created worksheets there are only enough rows to account for
> one sheet. *I believe it is overwriting the data from the first sheet
> with the data from the second sheet.
>
> Does anyone have any ideas how to append the data so that I get all of
> the information from both sheets?

Okay...Figured out part of the problem myself. This code was NOT set
up to loop through both worksheets like I thought. That was my
mistake. Question now is, how do I set it up to loop through both and
put all the data into the appropriate places?