split a large excel into smaller files but include the header in all files
1
I have a macro that I use to break large excel spreadsheets into smaller files. It works perfectly, except, it only uses the header row in the first file created and this header row (row 1) needs to be at the top of each new file. Is there a way to modify this code to somehow insert that row into all the files?
Sub SplitSheets()
' Save sheet in rows of 25000 to incremental CSV files
' JBeaucaire (7/27/2009)
Dim LR As Long, i As Long, Cntr As Long
Dim ws As Worksheet, OldDir As String
If MsgBox("Is this the sheet to parse data from?", vbYesNo + vbQuestion) = vbNo Then Exit Sub LR = Range("A" & Rows.Count).End(xlUp).row
Set ws = ActiveSheet
OldDir = CurDir 'memorizes the user's current working path
Dim v: v = Evaluate("ISREF(TEMP!A1)")
If Not v Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
Else
Sheets("Temp").Activate
Cells.Clear
End If
ChDir "C:UsersBartBDesktopsheets" 'path to save CSV file into
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
ChDir OldDir 'restores user's original working path
End Sub
microsoft-excel vba
edited Dec 26 '15 at 19:58
Hennes
59.2k793142
asked Sep 15 '11 at 17:28
bart burroughsbart burroughs
612
add a comment |
1
I have a macro that I use to break large excel spreadsheets into smaller files. It works perfectly, except, it only uses the header row in the first file created and this header row (row 1) needs to be at the top of each new file. Is there a way to modify this code to somehow insert that row into all the files?
Sub SplitSheets()
' Save sheet in rows of 25000 to incremental CSV files
' JBeaucaire (7/27/2009)
Dim LR As Long, i As Long, Cntr As Long
Dim ws As Worksheet, OldDir As String
If MsgBox("Is this the sheet to parse data from?", vbYesNo + vbQuestion) = vbNo Then Exit Sub LR = Range("A" & Rows.Count).End(xlUp).row
Set ws = ActiveSheet
OldDir = CurDir 'memorizes the user's current working path
Dim v: v = Evaluate("ISREF(TEMP!A1)")
If Not v Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
Else
Sheets("Temp").Activate
Cells.Clear
End If
ChDir "C:UsersBartBDesktopsheets" 'path to save CSV file into
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
ChDir OldDir 'restores user's original working path
End Sub
microsoft-excel vba
edited Dec 26 '15 at 19:58
Hennes
59.2k793142
asked Sep 15 '11 at 17:28
bart burroughsbart burroughs
612
add a comment |
1
1
1
I have a macro that I use to break large excel spreadsheets into smaller files. It works perfectly, except, it only uses the header row in the first file created and this header row (row 1) needs to be at the top of each new file. Is there a way to modify this code to somehow insert that row into all the files?
Sub SplitSheets()
' Save sheet in rows of 25000 to incremental CSV files
' JBeaucaire (7/27/2009)
Dim LR As Long, i As Long, Cntr As Long
Dim ws As Worksheet, OldDir As String
If MsgBox("Is this the sheet to parse data from?", vbYesNo + vbQuestion) = vbNo Then Exit Sub LR = Range("A" & Rows.Count).End(xlUp).row
Set ws = ActiveSheet
OldDir = CurDir 'memorizes the user's current working path
Dim v: v = Evaluate("ISREF(TEMP!A1)")
If Not v Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
Else
Sheets("Temp").Activate
Cells.Clear
End If
ChDir "C:UsersBartBDesktopsheets" 'path to save CSV file into
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
ChDir OldDir 'restores user's original working path
End Sub
microsoft-excel vba
edited Dec 26 '15 at 19:58
Hennes
59.2k793142
asked Sep 15 '11 at 17:28
bart burroughsbart burroughs
612
I have a macro that I use to break large excel spreadsheets into smaller files. It works perfectly, except, it only uses the header row in the first file created and this header row (row 1) needs to be at the top of each new file. Is there a way to modify this code to somehow insert that row into all the files?
Sub SplitSheets()
' Save sheet in rows of 25000 to incremental CSV files
' JBeaucaire (7/27/2009)
Dim LR As Long, i As Long, Cntr As Long
Dim ws As Worksheet, OldDir As String
If MsgBox("Is this the sheet to parse data from?", vbYesNo + vbQuestion) = vbNo Then Exit Sub LR = Range("A" & Rows.Count).End(xlUp).row
Set ws = ActiveSheet
OldDir = CurDir 'memorizes the user's current working path
Dim v: v = Evaluate("ISREF(TEMP!A1)")
If Not v Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
Else
Sheets("Temp").Activate
Cells.Clear
End If
ChDir "C:UsersBartBDesktopsheets" 'path to save CSV file into
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
ChDir OldDir 'restores user's original working path
End Sub
microsoft-excel vba
microsoft-excel vba
edited Dec 26 '15 at 19:58
Hennes
59.2k793142
asked Sep 15 '11 at 17:28
bart burroughsbart burroughs
612
edited Dec 26 '15 at 19:58
Hennes
59.2k793142
asked Sep 15 '11 at 17:28
bart burroughsbart burroughs
612
edited Dec 26 '15 at 19:58
Hennes
59.2k793142
edited Dec 26 '15 at 19:58
Hennes
59.2k793142
edited Dec 26 '15 at 19:58
Hennes
59.2k793142
59.2k793142
asked Sep 15 '11 at 17:28
bart burroughsbart burroughs
612
asked Sep 15 '11 at 17:28
bart burroughsbart burroughs
612
asked Sep 15 '11 at 17:28
bart burroughsbart burroughs
612
612
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
You will have to create the step in code. Right before the For Next loop write the code hard wired from row one to row one of the new file. After that just make sure you start writing at line two. This is accomplished by changing the line "For i = 2 to LR Step 2000"
ws.Rows("1:1").Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
Play with it but that is the idea.
answered Sep 15 '11 at 23:43
wbeard52wbeard52
2,73622238
Thanks very much for the reply but that didn't work. that creates a file with the header but no data and then a bunch of files with data but no header row. What I need is the header to be on every new file. i.e. I have a file of 20000 names, addresses, phones etc and want 10 files of 2000 each, but each one needs a header file (A1) which has the column headings for Company_Name, Company_Phone, etc. I'm not sure if VBA is capable of handling that or not.
– bart burroughs
Sep 21 '11 at 13:58
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f336043%2fsplit-a-large-excel-into-smaller-files-but-include-the-header-in-all-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You will have to create the step in code. Right before the For Next loop write the code hard wired from row one to row one of the new file. After that just make sure you start writing at line two. This is accomplished by changing the line "For i = 2 to LR Step 2000"
ws.Rows("1:1").Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
Play with it but that is the idea.
answered Sep 15 '11 at 23:43
wbeard52wbeard52
2,73622238
Thanks very much for the reply but that didn't work. that creates a file with the header but no data and then a bunch of files with data but no header row. What I need is the header to be on every new file. i.e. I have a file of 20000 names, addresses, phones etc and want 10 files of 2000 each, but each one needs a header file (A1) which has the column headings for Company_Name, Company_Phone, etc. I'm not sure if VBA is capable of handling that or not.
– bart burroughs
Sep 21 '11 at 13:58
add a comment |
0
You will have to create the step in code. Right before the For Next loop write the code hard wired from row one to row one of the new file. After that just make sure you start writing at line two. This is accomplished by changing the line "For i = 2 to LR Step 2000"
ws.Rows("1:1").Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
Play with it but that is the idea.
answered Sep 15 '11 at 23:43
wbeard52wbeard52
2,73622238
Thanks very much for the reply but that didn't work. that creates a file with the header but no data and then a bunch of files with data but no header row. What I need is the header to be on every new file. i.e. I have a file of 20000 names, addresses, phones etc and want 10 files of 2000 each, but each one needs a header file (A1) which has the column headings for Company_Name, Company_Phone, etc. I'm not sure if VBA is capable of handling that or not.
– bart burroughs
Sep 21 '11 at 13:58
add a comment |
0
0
0
You will have to create the step in code. Right before the For Next loop write the code hard wired from row one to row one of the new file. After that just make sure you start writing at line two. This is accomplished by changing the line "For i = 2 to LR Step 2000"
ws.Rows("1:1").Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
Play with it but that is the idea.
answered Sep 15 '11 at 23:43
wbeard52wbeard52
2,73622238
You will have to create the step in code. Right before the For Next loop write the code hard wired from row one to row one of the new file. After that just make sure you start writing at line two. This is accomplished by changing the line "For i = 2 to LR Step 2000"
ws.Rows("1:1").Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
For i = 1 To LR Step 2000
ws.Rows(i & ":" & i + 1999).Copy Range("A1")
Cntr = Cntr + 1
ActiveWorkbook.SaveAs Filename:="File" & Cntr & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Cells.Clear
Next i
Play with it but that is the idea.
answered Sep 15 '11 at 23:43
wbeard52wbeard52
2,73622238
answered Sep 15 '11 at 23:43
wbeard52wbeard52
2,73622238
answered Sep 15 '11 at 23:43
wbeard52wbeard52
2,73622238
answered Sep 15 '11 at 23:43
wbeard52wbeard52
2,73622238
2,73622238
Thanks very much for the reply but that didn't work. that creates a file with the header but no data and then a bunch of files with data but no header row. What I need is the header to be on every new file. i.e. I have a file of 20000 names, addresses, phones etc and want 10 files of 2000 each, but each one needs a header file (A1) which has the column headings for Company_Name, Company_Phone, etc. I'm not sure if VBA is capable of handling that or not.
– bart burroughs
Sep 21 '11 at 13:58
add a comment |
Thanks very much for the reply but that didn't work. that creates a file with the header but no data and then a bunch of files with data but no header row. What I need is the header to be on every new file. i.e. I have a file of 20000 names, addresses, phones etc and want 10 files of 2000 each, but each one needs a header file (A1) which has the column headings for Company_Name, Company_Phone, etc. I'm not sure if VBA is capable of handling that or not.
– bart burroughs
Sep 21 '11 at 13:58
Thanks very much for the reply but that didn't work. that creates a file with the header but no data and then a bunch of files with data but no header row. What I need is the header to be on every new file. i.e. I have a file of 20000 names, addresses, phones etc and want 10 files of 2000 each, but each one needs a header file (A1) which has the column headings for Company_Name, Company_Phone, etc. I'm not sure if VBA is capable of handling that or not.
– bart burroughs
Sep 21 '11 at 13:58
Thanks very much for the reply but that didn't work. that creates a file with the header but no data and then a bunch of files with data but no header row. What I need is the header to be on every new file. i.e. I have a file of 20000 names, addresses, phones etc and want 10 files of 2000 each, but each one needs a header file (A1) which has the column headings for Company_Name, Company_Phone, etc. I'm not sure if VBA is capable of handling that or not.
– bart burroughs
Sep 21 '11 at 13:58
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f336043%2fsplit-a-large-excel-into-smaller-files-but-include-the-header-in-all-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
