Can I split up an excel spreadsheet into multiple files by rows?

S

seespot

I have an excel spread sheet of many many rows and I would like to split
every row out into its own file. Is there an easy way to do this?
 
D

Dave Peterson

And do you want those files saved as .xls files or .txt files (or something
else)?
 
S

seespot

Each row is made up of numeric and text data in approximately 100 cells. I
would like to see each row in a new .csv of its own.
 
D

Dave Peterson

One way is with a macro:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set curWks = Worksheets("sheet1")
Set newWks = Workbooks.Add(1).Worksheets(1)

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
.Rows(iRow).Copy
With newWks.Range("A1")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
newWks.Parent.SaveAs _
Filename:="C:\temp\" & Format(iRow, "0000") & ".csv", _
FileFormat:=xlCSV
Next iRow
End With

newWks.Parent.Close savechanges:=False

End Sub

This routine expects to have an existing folder named c:\temp. It creates files
named ####.csv based on the row number.

If you run this a second time, you'll want to clean up that folder first--else
you'll be answer yes/no to each of the overwrite prompts.

I figure it's a safe way not to destroy important existing data.


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
S

seespot

Dave,

That is great. Thanks alot. I missed one thing though; row one is a header
row that needs to exist in every file as well. So... I need row one and row
two in the first file, then row one and row three in the next file, etc.

Can you help?
 
D

Dave Peterson

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set curWks = Worksheets("sheet1")
Set newWks = Workbooks.Add(1).Worksheets(1)

With curWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Rows(1).Copy
With newWks.Range("A1")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With

For iRow = FirstRow To LastRow
.Rows(iRow).Copy
With newWks.Range("a2")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
newWks.Parent.SaveAs _
Filename:="C:\temp\" & Format(iRow, "0000") & ".csv", _
FileFormat:=xlCSV
Next iRow
End With

newWks.Parent.Close savechanges:=False

End Sub

Dave,

That is great. Thanks alot. I missed one thing though; row one is a header
row that needs to exist in every file as well. So... I need row one and row
two in the first file, then row one and row three in the next file, etc.

Can you help?
Click to expand...
 
A

afaubert

Dave, I am trying to take a worksheet and split it by the field in the first
column to create separate spreadsheets ...basically I have a master and want
to split it out to separate files with a naming convention within the
worksheet (mgr name) in the worksheet. How can I modify this macro to do
that. I want to maintain all the formatting/formulas/locked cells from the
original master worksheet.

Thanks
Allison
 
D

Dave Peterson

I don't think I'd use this macro as the basis for what you want.

But maybe it's already built:

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

And Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm
Dave, I am trying to take a worksheet and split it by the field in the first
column to create separate spreadsheets ...basically I have a master and want
to split it out to separate files with a naming convention within the
worksheet (mgr name) in the worksheet. How can I modify this macro to do
that. I want to maintain all the formatting/formulas/locked cells from the
original master worksheet.

Thanks
Allison
Click to expand...
 
Top