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

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?

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

Give us an example of one of your rows. What are your files going to
contain.


--
jayd77
------------------------------------------------------------------------
jayd77's Profile: http://www.excelforum.com/member.php...o&userid=19048
View this thread: http://www.excelforum.com/showthread...hreadid=493219

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

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

seespot wrote:

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?

--

Dave Peterson

Can I split up an excel spreadsheet into multiple files by row
 

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.

"Dave Peterson" wrote:

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

seespot wrote:

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?

--

Dave Peterson

Can I split up an excel spreadsheet into multiple files by row
 

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

seespot wrote:

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.

"Dave Peterson" wrote:

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

seespot wrote:

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?

--

Dave Peterson


--

Dave Peterson

Can I split up an excel spreadsheet into multiple files by row
 

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?

"Dave Peterson" wrote:

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

seespot wrote:

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.

"Dave Peterson" wrote:

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

seespot wrote:

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?

--

Dave Peterson


--

Dave Peterson

Can I split up an excel spreadsheet into multiple files by row
 

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


seespot wrote:

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?

"Dave Peterson" wrote:

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

seespot wrote:

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.

"Dave Peterson" wrote:

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

seespot wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Can I split up an excel spreadsheet into multiple files by row
 

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


"Dave Peterson" wrote:

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


seespot wrote:

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?

"Dave Peterson" wrote:

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

seespot wrote:

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.

"Dave Peterson" wrote:

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

seespot wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Can I split up an excel spreadsheet into multiple files by row
 

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

afaubert wrote:

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

"Dave Peterson" wrote:

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


seespot wrote:

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?

"Dave Peterson" wrote:

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

seespot wrote:

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.

"Dave Peterson" wrote:

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

seespot wrote:

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?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson