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?

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

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

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
>

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,

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
>

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

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

Originally Posted by 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

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

Is there a way to keep header as common in every sheet and split rows if it exceed more than 2000 rows in to different sheet and so on!

Originally Posted by 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

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, thanks for this response. How do I modify this macro to separate the original file to multiple files but not 1 row each (plus the top row) but indicate the exact number of rows in addition to that top row? For instance, I have 5001 rows file and want to create multiple files 1001 rows each with the same top row.

Thanks again for your help!

Oh, I've just found the answer to my question here:
https://www.magnetismsolutions.com/b...ata-migrations
Tried it and it does what I need as described above, so I am good now.

Will do next time, thanks for letting me know.