How can I separate a large spreadsheet into worksheets?
 

I have a large .CSV spreadsheet that shows all of sales history for the
past 12 months. I would like to separate that data into bottom-tabbed
worksheets. I will end up with about 100 worksheets. Our 10-digit customer
code is the first column in the spreadsheet.

When this report was run once a month, I just cut and pasted the data
into separate worksheets manually. However, now the bosses want to be able
to run this report on demand.

If it would make the problem easier, I could break the data down into
separate spreadsheets wneh I export it from the filePro DBMS system. IE,
acmeco.csv, jonesinc.csv, smith.csv, etc.

Thanks!

How can I separate a large spreadsheet into worksheets?
 

Mike,

Import the CSV file into an Excel workbook, then run the macro below.

HTH,
Bernie
MS Excel MVP

Sub ExportSheetsFromDatabase()
'Based on the value in the first column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range

Set myArea = Range("A1").CurrentRegion.Columns(1).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
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub



"Mike Schwartz" wrote in message
...
I have a large .CSV spreadsheet that shows all of sales history for the
past 12 months. I would like to separate that data into bottom-tabbed
worksheets. I will end up with about 100 worksheets. Our 10-digit customer
code is the first column in the spreadsheet.

When this report was run once a month, I just cut and pasted the data
into separate worksheets manually. However, now the bosses want to be able
to run this report on demand.

If it would make the problem easier, I could break the data down into
separate spreadsheets wneh I export it from the filePro DBMS system. IE,
acmeco.csv, jonesinc.csv, smith.csv, etc.

Thanks!

How can I separate a large spreadsheet into worksheets?
 

Hi mike

See also
http://www.rondebruin.nl/copy5.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mike Schwartz" wrote in message ...
I have a large .CSV spreadsheet that shows all of sales history for the
past 12 months. I would like to separate that data into bottom-tabbed
worksheets. I will end up with about 100 worksheets. Our 10-digit customer
code is the first column in the spreadsheet.

When this report was run once a month, I just cut and pasted the data
into separate worksheets manually. However, now the bosses want to be able
to run this report on demand.

If it would make the problem easier, I could break the data down into
separate spreadsheets wneh I export it from the filePro DBMS system. IE,
acmeco.csv, jonesinc.csv, smith.csv, etc.

Thanks!

How can I separate a large spreadsheet into worksheets?
 

Cool! This worked perfectly! It even put the header row at the top of
each worksheet!

Where do I send the Pizza Hut Gift Card???

Mike


"Bernie Deitrick" wrote:

Mike,

Import the CSV file into an Excel workbook, then run the macro below.

HTH,
Bernie
MS Excel MVP

Sub ExportSheetsFromDatabase()
'Based on the value in the first column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range

Set myArea = Range("A1").CurrentRegion.Columns(1).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
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub



"Mike Schwartz" wrote in message
...
I have a large .CSV spreadsheet that shows all of sales history for the
past 12 months. I would like to separate that data into bottom-tabbed
worksheets. I will end up with about 100 worksheets. Our 10-digit customer
code is the first column in the spreadsheet.

When this report was run once a month, I just cut and pasted the data
into separate worksheets manually. However, now the bosses want to be able
to run this report on demand.

If it would make the problem easier, I could break the data down into
separate spreadsheets wneh I export it from the filePro DBMS system. IE,
acmeco.csv, jonesinc.csv, smith.csv, etc.

Thanks!

How can I separate a large spreadsheet into worksheets?
 

Thanks! Bernie's macro did the trick, but I'm going to bookmark your
page just in case I need to modify it.

Mike Schwartz

"Ron de Bruin" wrote:

Hi mike

See also
http://www.rondebruin.nl/copy5.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mike Schwartz" wrote in message ...
I have a large .CSV spreadsheet that shows all of sales history for the
past 12 months. I would like to separate that data into bottom-tabbed
worksheets. I will end up with about 100 worksheets. Our 10-digit customer
code is the first column in the spreadsheet.

When this report was run once a month, I just cut and pasted the data
into separate worksheets manually. However, now the bosses want to be able
to run this report on demand.

If it would make the problem easier, I could break the data down into
separate spreadsheets wneh I export it from the filePro DBMS system. IE,
acmeco.csv, jonesinc.csv, smith.csv, etc.

Thanks!

How can I separate a large spreadsheet into worksheets?
 

Where do I send the Pizza Hut Gift Card???

Getting a "Thanks!" is sufficient...

Thanks,
Bernie