On Mar 7, 6:50*pm, JLGWhiz 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(befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
* * .AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
* * .SpecialCells(xlCellTypeVisible).Copy _
* * * * mySht.Range("A1")
* * mySht.Cells.EntireColumn.AutoFit
* * .AutoFilter
End With
Resume
SheetExists:
Next myCell

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