I have a downloaded file that has employee data( area,name) E.g.
A B
cellar Smith
tower Jones
floor Lee
cellar adams
tower Miller

I want to create tabs for each location, and want the cellar tab to show
Smith in row 1, Adams in row 2, & the tower tab to show Jones in row 1,
Miller in row 2, etc. I tried to create a table with the locations and
tried Vlookup, but obviously I'm doing something wrong because I'm getting
all rows showing Smith

Any help would be appreciated.

Thanks,

Steve

Steve,

How about a macro? Select your table, then answer 1 when asked "What column # within database to
use as key?" This assumes that your data has a row of headers.....

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

"Steve" wrote in message
...
I have a downloaded file that has employee data( area,name) E.g.
A B
cellar Smith
tower Jones
floor Lee
cellar adams
tower Miller

I want to create tabs for each location, and want the cellar tab to show
Smith in row 1, Adams in row 2, & the tower tab to show Jones in row 1,
Miller in row 2, etc. I tried to create a table with the locations and
tried Vlookup, but obviously I'm doing something wrong because I'm getting
all rows showing Smith

Any help would be appreciated.

Thanks,

Steve

I don't understand the macro at all, but I copied it, and I get this error:
Cannot rename a sheet to the same name as another sheet, a referenced object
library or a workbook referenced in visual basic.
The debug shows it hanging at this line - mySht.Name = myCell.Value

"Bernie Deitrick" wrote:

Steve,

How about a macro? Select your table, then answer 1 when asked "What column # within database to
use as key?" This assumes that your data has a row of headers.....

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

"Steve" wrote in message
...
I have a downloaded file that has employee data( area,name) E.g.
A B
cellar Smith
tower Jones
floor Lee
cellar adams
tower Miller

I want to create tabs for each location, and want the cellar tab to show
Smith in row 1, Adams in row 2, & the tower tab to show Jones in row 1,
Miller in row 2, etc. I tried to create a table with the locations and
tried Vlookup, but obviously I'm doing something wrong because I'm getting
all rows showing Smith

Any help would be appreciated.

Thanks,

Steve

Steve,

The macro is trying to make a sheet for each of your categories: If you have
characters that are not allowed in a sheet name, you will get that error.

If you select the myCell.Value, what is the value that shows on the
tooltip-text?

HTH,
Bernie
MS Excel MVP


"Steve" wrote in message
...
I don't understand the macro at all, but I copied it, and I get this error:
Cannot rename a sheet to the same name as another sheet, a referenced
object
library or a workbook referenced in visual basic.
The debug shows it hanging at this line - mySht.Name = myCell.Value

"Bernie Deitrick" wrote:

Steve,

How about a macro? Select your table, then answer 1 when asked "What
column # within database to
use as key?" This assumes that your data has a row of headers.....

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

"Steve" wrote in message
...
I have a downloaded file that has employee data( area,name) E.g.
A B
cellar Smith
tower Jones
floor Lee
cellar adams
tower Miller

I want to create tabs for each location, and want the cellar tab to
show
Smith in row 1, Adams in row 2, & the tower tab to show Jones in row 1,
Miller in row 2, etc. I tried to create a table with the locations
and
tried Vlookup, but obviously I'm doing something wrong because I'm
getting
all rows showing Smith

Any help would be appreciated.

Thanks,

Steve

Bernie,

I'm totally confused, as I'm only familiar with VB from recording macros.
Also, I apologize because I re-read my question and I indicated that I wanted
to create tabs for each location. That was mis-stated, as I had already
created the tabs for those locations as needed, and didn't even realize that
a macro could have done it. So, with those tabs already created, all I needed
was something to put all the cellar people in the cellar tab, all the tower
people in the tower tab, etc. Again, sorry my unclear request caused you
extra work.

Thanks,

Steve

"Bernie Deitrick" wrote:

Steve,

The macro is trying to make a sheet for each of your categories: If you have
characters that are not allowed in a sheet name, you will get that error.

If you select the myCell.Value, what is the value that shows on the
tooltip-text?

HTH,
Bernie
MS Excel MVP


"Steve" wrote in message
...
I don't understand the macro at all, but I copied it, and I get this error:
Cannot rename a sheet to the same name as another sheet, a referenced
object
library or a workbook referenced in visual basic.
The debug shows it hanging at this line - mySht.Name = myCell.Value

"Bernie Deitrick" wrote:

Steve,

How about a macro? Select your table, then answer 1 when asked "What
column # within database to
use as key?" This assumes that your data has a row of headers.....

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

"Steve" wrote in message
...
I have a downloaded file that has employee data( area,name) E.g.
A B
cellar Smith
tower Jones
floor Lee
cellar adams
tower Miller

I want to create tabs for each location, and want the cellar tab to
show
Smith in row 1, Adams in row 2, & the tower tab to show Jones in row 1,
Miller in row 2, etc. I tried to create a table with the locations
and
tried Vlookup, but obviously I'm doing something wrong because I'm
getting
all rows showing Smith

Any help would be appreciated.

Thanks,

Steve

Steve,

Try the new version of the macro below.

HTH,
Bernie
MS Excel MVP


Sub ExportDatabaseExistingSheets()
'Export is based on the value in the desired column
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim KeyCol As Integer

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 mySht In ActiveWorkbook.Worksheets
myName = mySht.Name
With myArea.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myName
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Next mySht

End Sub



"Steve" wrote in message
...
Bernie,

I'm totally confused, as I'm only familiar with VB from recording macros.
Also, I apologize because I re-read my question and I indicated that I wanted
to create tabs for each location. That was mis-stated, as I had already
created the tabs for those locations as needed, and didn't even realize that
a macro could have done it. So, with those tabs already created, all I needed
was something to put all the cellar people in the cellar tab, all the tower
people in the tower tab, etc. Again, sorry my unclear request caused you
extra work.

Thanks,

Steve

"Bernie Deitrick" wrote:

Steve,

The macro is trying to make a sheet for each of your categories: If you have
characters that are not allowed in a sheet name, you will get that error.

If you select the myCell.Value, what is the value that shows on the
tooltip-text?

HTH,
Bernie
MS Excel MVP


"Steve" wrote in message
...
I don't understand the macro at all, but I copied it, and I get this error:
Cannot rename a sheet to the same name as another sheet, a referenced
object
library or a workbook referenced in visual basic.
The debug shows it hanging at this line - mySht.Name = myCell.Value

"Bernie Deitrick" wrote:

Steve,

How about a macro? Select your table, then answer 1 when asked "What
column # within database to
use as key?" This assumes that your data has a row of headers.....

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

"Steve" wrote in message
...
I have a downloaded file that has employee data( area,name) E.g.
A B
cellar Smith
tower Jones
floor Lee
cellar adams
tower Miller

I want to create tabs for each location, and want the cellar tab to
show
Smith in row 1, Adams in row 2, & the tower tab to show Jones in row 1,
Miller in row 2, etc. I tried to create a table with the locations
and
tried Vlookup, but obviously I'm doing something wrong because I'm
getting
all rows showing Smith

Any help would be appreciated.

Thanks,

Steve