Parse Data

[Steph]

Hi everyone. I have a single worksheet that is sort of a database. I t has
several thousand rows and about 20 columns. One column is Supervisor name.
I need to separate the single sheet into multiple FILES (1 per Supervisor)
based on Supervisor name. Can you help? Supervisor name is in column C.
Thanks!

 

[Bernie Deitrick]

Steph,

Copy the macro below and put it into a codemodule of either your
personal.xls or of the workbook with the database.

Select a single cell in your database and run the macro. If you supervisor
names are in column C, and column C is the second column of the database,
then enter a 2 when asked "What column # within database to use as key?"

The files will be saved to whatever folder is currently the default folder,
though that is easy to modify.

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

 

[Steph]

Hi Bernie. Thanks for the code. I tried it, and got an error on this line:
mySht.Name = myCell.Value
The error was Object variable or with block variable not set.

It looks like it is parsing the data correctly, but errors out at the very
end. Any ideas? Am I doing something wrong? Thanks!!

-Steph

 

[Bernie Deitrick]

Steph,

You must have a blank cell in your data column, or a name that has an
invalid sheetname character. Sheet names can't include characters like /\ '
[ ] and a few others that I don't recall off-hand right now.

Run the code, and when you get the error, go into debug mode. With your
cursor, highlight myCell.Value. A little pop-up box should show the value,
or you could add a watch to see the value.

If it is a blank (""), try adding

If myCell.Value = "" Then GoTo SheetExists

just below the line

For Each myCell In myArea

Otherwise, we'll need to add code to watch for invalid characters in the
sheet name.

HTH,
Bernie
MS Excel MVP

 

[Steph]

Thanks Bernie. I think you hit it on the head. I neglected to mention this
in the original post, but the database has 4 header lines (3 of which are
blank i the key column). The actual data begins on row 5. But I'd like (if
possible) the 4 data lines to be on each of the newly created files as well.
Is this an easy fix?

Steph,

You must have a blank cell in your data column, or a name that has an
invalid sheetname character. Sheet names can't include characters like /\ '
[ ] and a few others that I don't recall off-hand right now.

Run the code, and when you get the error, go into debug mode. With your
cursor, highlight myCell.Value. A little pop-up box should show the value,
or you could add a watch to see the value.

If it is a blank (""), try adding

If myCell.Value = "" Then GoTo SheetExists

just below the line

For Each myCell In myArea

Otherwise, we'll need to add code to watch for invalid characters in the
sheet name.

HTH,
Bernie
MS Excel MVP

Hi Bernie. Thanks for the code. I tried it, and got an error on this line:
mySht.Name = myCell.Value
The error was Object variable or with block variable not set.

It looks like it is parsing the data correctly, but errors out at the very
end. Any ideas? Am I doing something wrong? Thanks!!

-Steph

I

t column
C.

 

[Bernie Deitrick]

Steph,

I can only tell you that we could do it, but it would be better if you used
better spreadsheet design. Blanks are BAD in databases, and multiple rows
or headers are _really_ BAD.

Instead of using separate cells to put your header, use a single cell with
Alt-Enter between the lines that you want to show, to control how the text
wraps. For example, instead of

Header1
Header2

in two cells, you would type Header1, press Alt-Enter, then type Header2,
and then press Enter. That would keep your headers on sepearate rows within
the same cell.

If you can't change this for some reason (like there are thousands of these
bad databases out there that you need to do) post back and we'll do
something.

HTH,
Bernie
MS Excel MVP

Thanks Bernie. I think you hit it on the head. I neglected to mention this
in the original post, but the database has 4 header lines (3 of which are
blank i the key column). The actual data begins on row 5. But I'd like (if
possible) the 4 data lines to be on each of the newly created files as well.
Is this an easy fix?

Steph,

You must have a blank cell in your data column, or a name that has an
invalid sheetname character. Sheet names can't include characters like

/\

'
[ ] and a few others that I don't recall off-hand right now.

Run the code, and when you get the error, go into debug mode. With your
cursor, highlight myCell.Value. A little pop-up box should show the value,
or you could add a watch to see the value.

If it is a blank (""), try adding

If myCell.Value = "" Then GoTo SheetExists

just below the line

For Each myCell In myArea

Otherwise, we'll need to add code to watch for invalid characters in the
sheet name.

HTH,
Bernie
MS Excel MVP

Hi Bernie. Thanks for the code. I tried it, and got an error on this line:
mySht.Name = myCell.Value
The error was Object variable or with block variable not set.

It looks like it is parsing the data correctly, but errors out at the very
end. Any ideas? Am I doing something wrong? Thanks!!

-Steph

Steph,

Copy the macro below and put it into a codemodule of either your
personal.xls or of the workbook with the database.

Select a single cell in your database and run the macro. If you
supervisor
names are in column C, and column C is the second column of the database,
then enter a 2 when asked "What column # within database to use as key?"

The files will be saved to whatever folder is currently the default
folder,
though that is easy to modify.

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

If myCell.Value = "" Then GoTo SheetExists

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


Hi everyone. I have a single worksheet that is sort of a

database.

I

 

[Steph]

Bernie,

Thank you SO much for your help. Would this be easier? Instead of parsing
the data and creating NEW workbooks, what if I had workbooks already created
and named as the names in the key column. I could put the headers there,
and therefore remove them from the database.

Actually, that would make life easier for me, becasue I also needed to add
before save events to all the workbooks we were creating. I WAS going to do
that via code, but already having it within the workbook would be a snap.

Is that easier?

Thanks again!!

-Steph

Steph,

I can only tell you that we could do it, but it would be better if you used
better spreadsheet design. Blanks are BAD in databases, and multiple rows
or headers are _really_ BAD.

Instead of using separate cells to put your header, use a single cell with
Alt-Enter between the lines that you want to show, to control how the text
wraps. For example, instead of

Header1
Header2

in two cells, you would type Header1, press Alt-Enter, then type Header2,
and then press Enter. That would keep your headers on sepearate rows within
the same cell.

If you can't change this for some reason (like there are thousands of these
bad databases out there that you need to do) post back and we'll do
something.

HTH,
Bernie
MS Excel MVP

Thanks Bernie. I think you hit it on the head. I neglected to mention this
in the original post, but the database has 4 header lines (3 of which are
blank i the key column). The actual data begins on row 5. But I'd like (if
possible) the 4 data lines to be on each of the newly created files as well.
Is this an easy fix?

like

/\

'
[ ] and a few others that I don't recall off-hand right now.

Run the code, and when you get the error, go into debug mode. With your
cursor, highlight myCell.Value. A little pop-up box should show the value,
or you could add a watch to see the value.

If it is a blank (""), try adding

If myCell.Value = "" Then GoTo SheetExists

just below the line

For Each myCell In myArea

Otherwise, we'll need to add code to watch for invalid characters in the
sheet name.

HTH,
Bernie
MS Excel MVP

Hi Bernie. Thanks for the code. I tried it, and got an error on this
line:
mySht.Name = myCell.Value
The error was Object variable or with block variable not set.

It looks like it is parsing the data correctly, but errors out at

the
very

end. Any ideas? Am I doing something wrong? Thanks!!

-Steph

Steph,

Copy the macro below and put it into a codemodule of either your
personal.xls or of the workbook with the database.

Select a single cell in your database and run the macro. If you
supervisor
names are in column C, and column C is the second column of the
database,
then enter a 2 when asked "What column # within database to use as key?"

The files will be saved to whatever folder is currently the default
folder,
though that is easy to modify.

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
If myCell.Value = "" Then GoTo SheetExists
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


Hi everyone. I have a single worksheet that is sort of a

database.

I
t
has
several thousand rows and about 20 columns. One column is Supervisor
name.
I need to separate the single sheet into multiple FILES (1 per
Supervisor)
based on Supervisor name. Can you help? Supervisor name is in column
C.
Thanks!

 

[Bernie Deitrick]

Steph,

A database without headers is an _extremely_ BAD thing <vbg>.

If you post the code that you wanted to add to the workbooks that are being
created, on Monday I will modify the code to use four header rows, and to
add the code to the workbooks as they are created.

Just tell me one thing: when you have your four rows of headers, with three
rows blank, do you have

Blank
Blank
Blank
Header

or do you have

Header
Blank
Blank
Blank

Talk to you Monday...

Bernie
MS Excel MVP

 

[Steph]

Hi Bernie,

I REALLY appreciate your help! I've been learning quite a bit from the
experts on this board...slowly but surely!

As far as your previous questions, the headers went blank, blank, balnk,
header. BUT, I really didn't need the 3 blank rows on this database (since
it's already a rollup from many other files), so I removed them. When I
re-ran, I still got the error. In debug mode, Mycell.value=<object variable
or with block variable not set>. I made sure the database had no empty
cells or invalid data characters.

As for the code to be added to the new files, I wanted a click event or
before save event that would generte an e-mail to a specific person when the
event triggered. I found the code to add a before save event:
Sub Add_Event_Proc()

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With

End Sub

I think a click event would work better for me, but that shouldn't be too
tough for me to figure out.

Thanks Bernie!!!

-Steph

 

[Steph]

Bernie,

I figured out the problem....I had a bunch of hidden sheets. So when I
stepped through you code, I saw that one of the hidden sheets was being
referenced in a variable, and that's when it errored out. So I deleted all
the sheets, and it works great!! (they didn't need to be there
anyway...there are many steps and files in this process, so while I was
editing this I wanted to keep everything together. In reality, this
particular database file will onlt have the 1 sheet.)

I'm still hoping you can help me with having vba write a click event
procedure in the newly created files. Thanks Bernie!!!!

 

[Bernie Deitrick]

Steph,

Do you want a click-event (which is worksheet based) or a before save event
(which is workbook based)? How they would be added would be dependent on
your choice.

HTH,
Bernie
MS Excel MVP

 

[Steph]

Hi Bernie,

I think a click event would be best. The user may go into the file more
than once, so I don't want the code to execute every time they close and
save the workbook.

Also, going back to the blank, blank, blank, header issue......is that an
easy modification to your code? It would be interesting to see how you
would make that work.

Thanks again!

-Steph