I am running a report on a monthly basis, which may contain up to 50,000
rows. I need to load the report results into another application, but that
application has a line limit of 10,000 records.
Is there a way to easily cut / copy my large file into multiple smaller
files? Most preferably, the smaller file would only contain columns A, B and
C of the original file.
thanks!
Eric

I don't know if this'll help you or not, but it would take groups of 10000
rows and put them into separate worksheets in the book and you could copy
from those into the other application?
Use [Alt]+[F11] to open the VB Editor, then use Insert | Module to start a
new code module and cut and paste the code below into it. Close the VB
Editor, choose the sheet with the data on it and then Tools | Macro | Macros
and choose this macro and hit the [Run] button. The data will be written in
groups of 'copySize' rows into separate sheets in the workbook.

Sub PrepareForPaste()
Const copySize = 10000 Dim repeatCount As Integer
Dim startRow As Long
Dim endRow As Long
Dim lastRow As Long
Dim LC As Integer
Dim sourceSheet As String

sourceSheet = ActiveSheet.Name
lastRow = Range("A" & Rows.Count).End(xlUp).Row
repeatCount = Int(lastRow / copySize)
For LC = 1 To repeatCount
startRow = (LC - 1) * copySize + 1
endRow = LC * copySize
Worksheets(sourceSheet).Range("A" & startRow & ":C" & endRow).Copy
Worksheets.Add
Range("A1").PasteSpecial xlPasteAll
Next
End Sub


"Eric S." wrote:

I am running a report on a monthly basis, which may contain up to 50,000
rows. I need to load the report results into another application, but that
application has a line limit of 10,000 records.
Is there a way to easily cut / copy my large file into multiple smaller
files? Most preferably, the smaller file would only contain columns A, B and
C of the original file.
thanks!
Eric

thanks for the fast response!
unfortunately, when running the macro I get a "Compile Error: Syntax error"
would you mind taking another look?
thanks!
Eric

"JLatham" wrote:

I don't know if this'll help you or not, but it would take groups of 10000
rows and put them into separate worksheets in the book and you could copy
from those into the other application?
Use [Alt]+[F11] to open the VB Editor, then use Insert | Module to start a
new code module and cut and paste the code below into it. Close the VB
Editor, choose the sheet with the data on it and then Tools | Macro | Macros
and choose this macro and hit the [Run] button. The data will be written in
groups of 'copySize' rows into separate sheets in the workbook.

Sub PrepareForPaste()
Const copySize = 10000 Dim repeatCount As Integer
Dim startRow As Long
Dim endRow As Long
Dim lastRow As Long
Dim LC As Integer
Dim sourceSheet As String

sourceSheet = ActiveSheet.Name
lastRow = Range("A" & Rows.Count).End(xlUp).Row
repeatCount = Int(lastRow / copySize)
For LC = 1 To repeatCount
startRow = (LC - 1) * copySize + 1
endRow = LC * copySize
Worksheets(sourceSheet).Range("A" & startRow & ":C" & endRow).Copy
Worksheets.Add
Range("A1").PasteSpecial xlPasteAll
Next
End Sub


"Eric S." wrote:

I am running a report on a monthly basis, which may contain up to 50,000
rows. I need to load the report results into another application, but that
application has a line limit of 10,000 records.
Is there a way to easily cut / copy my large file into multiple smaller
files? Most preferably, the smaller file would only contain columns A, B and
C of the original file.
thanks!
Eric

I see what's wrong! when I pasted it here, I dropped a line feed.
put the Dim repeatCount As Integer on a line by itself, simply go into the
code, put your cursor in front of Dim and press enter.

It should work then - I actually copied that from working code that I
tested, I just somehow screwed up the paste - probably by editing 100 to read
as 10000 since I was only using 500 rows of information.

"Eric S." wrote:

thanks for the fast response!
unfortunately, when running the macro I get a "Compile Error: Syntax error"
would you mind taking another look?
thanks!
Eric

"JLatham" wrote:

I don't know if this'll help you or not, but it would take groups of 10000
rows and put them into separate worksheets in the book and you could copy
from those into the other application?
Use [Alt]+[F11] to open the VB Editor, then use Insert | Module to start a
new code module and cut and paste the code below into it. Close the VB
Editor, choose the sheet with the data on it and then Tools | Macro | Macros
and choose this macro and hit the [Run] button. The data will be written in
groups of 'copySize' rows into separate sheets in the workbook.

Sub PrepareForPaste()
Const copySize = 10000 Dim repeatCount As Integer
Dim startRow As Long
Dim endRow As Long
Dim lastRow As Long
Dim LC As Integer
Dim sourceSheet As String

sourceSheet = ActiveSheet.Name
lastRow = Range("A" & Rows.Count).End(xlUp).Row
repeatCount = Int(lastRow / copySize)
For LC = 1 To repeatCount
startRow = (LC - 1) * copySize + 1
endRow = LC * copySize
Worksheets(sourceSheet).Range("A" & startRow & ":C" & endRow).Copy
Worksheets.Add
Range("A1").PasteSpecial xlPasteAll
Next
End Sub


"Eric S." wrote:

I am running a report on a monthly basis, which may contain up to 50,000
rows. I need to load the report results into another application, but that
application has a line limit of 10,000 records.
Is there a way to easily cut / copy my large file into multiple smaller
files? Most preferably, the smaller file would only contain columns A, B and
C of the original file.
thanks!
Eric

GREAT - this solved the issue.

thanks again for your help!


"JLatham" wrote:

I see what's wrong! when I pasted it here, I dropped a line feed.
put the Dim repeatCount As Integer on a line by itself, simply go into the
code, put your cursor in front of Dim and press enter.

It should work then - I actually copied that from working code that I
tested, I just somehow screwed up the paste - probably by editing 100 to read
as 10000 since I was only using 500 rows of information.

"Eric S." wrote:

thanks for the fast response!
unfortunately, when running the macro I get a "Compile Error: Syntax error"
would you mind taking another look?
thanks!
Eric

"JLatham" wrote:

I don't know if this'll help you or not, but it would take groups of 10000
rows and put them into separate worksheets in the book and you could copy
from those into the other application?
Use [Alt]+[F11] to open the VB Editor, then use Insert | Module to start a
new code module and cut and paste the code below into it. Close the VB
Editor, choose the sheet with the data on it and then Tools | Macro | Macros
and choose this macro and hit the [Run] button. The data will be written in
groups of 'copySize' rows into separate sheets in the workbook.

Sub PrepareForPaste()
Const copySize = 10000 Dim repeatCount As Integer
Dim startRow As Long
Dim endRow As Long
Dim lastRow As Long
Dim LC As Integer
Dim sourceSheet As String

sourceSheet = ActiveSheet.Name
lastRow = Range("A" & Rows.Count).End(xlUp).Row
repeatCount = Int(lastRow / copySize)
For LC = 1 To repeatCount
startRow = (LC - 1) * copySize + 1
endRow = LC * copySize
Worksheets(sourceSheet).Range("A" & startRow & ":C" & endRow).Copy
Worksheets.Add
Range("A1").PasteSpecial xlPasteAll
Next
End Sub


"Eric S." wrote:

I am running a report on a monthly basis, which may contain up to 50,000
rows. I need to load the report results into another application, but that
application has a line limit of 10,000 records.
Is there a way to easily cut / copy my large file into multiple smaller
files? Most preferably, the smaller file would only contain columns A, B and
C of the original file.
thanks!
Eric