Maybe Vlookup ? But.....

[Guest]

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 Deitrick]

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

End Sub

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Ok, getting closer. The macro ran without hanging up, but it's not copying
the correct data. Maybe I'm unclear as to what I need to have selected what I
run the macro.

In the table tab, I have cellar in A2 & Tower in A3
In my data tab, I have Cellar or tower in the A column, employee ID # in the
B column, and employee Name in the C column (column heading in row 2, and
data starts in row 3).
In my cellar and tower tabs, I want
the data from the data tab to copy from the data tab as follows:
Data tab: If A is cellar,ID # from the B column to B9 of the cellar tab,
then for the next employee that is in the cellar,B10 of the cellar tab, etc (
If A is tower, the same refs as above on the tower tab),Etc. -
Basically if a = tower then B, C & D from the data tab to B99 on the tower
tab, Then 10, then 11, etc. as many as needed; and if a = cellar then B, C &
D from the data tab to B99 on the cellar tab, then 10, then 11, etc ( as
many as needed).
I realize I may not be explaining this well, and if you want to bail out, I
wouldn't blame you. And again, thanks for all your patience.

Steve

 

[Bernie Deitrick]

Steve,

I think you would be better off having only the data sheet, then using data filters to show the data
that you are interested in. There are just too many ways to mess up data when it resides on
multiple sheets....

HTH,
Bernie
MS Excel MVP

 

[Guest]

And I could understand filtering I defined names of the data range, macro
filtered, and it works great. Thanks again for all your help, and especially
your patience.

Steve