Macro to break up a csv file

L

Les

Please help
This is a few lines of a csv file that I have. I have it
open in an Excel workbook. I need to write/record a macro
that creates a new worksheet and cuts and pastes the data
into them at every line that contains an 'R' on the left
hand side. The amount of rows that contain data after
the 'R' row are variable throughout the file.
Thank you.

H 11/12/2003 08:00:12
R 7926 1601 N B 11/12/2003
11/12/2003 10:15
L BX10A T10 2 18 6
L BX10A T10 5 71 29
L T20 43 63 22
L T21 BM 9 1
L T21 MB 6 1
L T21 11 17 3
L T21 13 14 2
L T21 14 7 2
L T27 87 17 7
L T27 88 19 8
L T30 74 7 7
L T30 76 2 2
L T32 47 20 16
L T33 16 14 13
L T33 19 17 15
L T33 22 3 3
L T38 AT 8 6
L T38 BT 1 1
L T82 73 16 12
L T83 71 2 2
L T83 83 3 1
L T86 12 6 6
R 7927 1494 N H 11/12/2003
11/12/2003 10:20
L T16 301 1 1
L T17 302 6 6
L T18 201 11 11
L T25 204 2 2
L T28 203 31 17
L T30 107 30 22
L T32 304 20 19
L T37 206 27 24
L T37 208 25 20
L T37 209 7 7
L T38 105 71 32
L T41 104 212 60
L T57 205 13 13
L T57 207 7 7
L T81 200 1 1
L T86 300 2 2
L T93 306 104 54
R 7927 1494 N B 11/12/2003
11/12/2003 10:20
L BX10A T10 2 9 6
L BX10A T10 5 35 23
L BX21A T21 20 76 37
 
D

Don Guillett

Make a backup of your file first and try this.
Assumes that your master worksheet is named csvfile

Sub moveRs()
Do Until Cells(Rows.Count, "a").End(xlUp).Row < 3
x = Application.Match(1, Range("A:A"), -1) - 1
'MsgBox x
y = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox y
newname = Left(Cells(x, 1), 15)
'MsgBox newname
Set NewWs = Worksheets.Add
NewWs.Name = newname
Sheets("csvfile").Select
Sheets("csvfile").Range(Cells(x, 1), Cells(y, 1)).Cut
Sheets([newname]).Range("a1")
Loop
End Sub
 
D

Don Guillett

When you copy/paste this
After the Cut add a space and underscore
Cut _

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don Guillett said:
Make a backup of your file first and try this.
Assumes that your master worksheet is named csvfile

Sub moveRs()
Do Until Cells(Rows.Count, "a").End(xlUp).Row < 3
x = Application.Match(1, Range("A:A"), -1) - 1
'MsgBox x
y = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox y
newname = Left(Cells(x, 1), 15)
'MsgBox newname
Set NewWs = Worksheets.Add
NewWs.Name = newname
Sheets("csvfile").Select
Sheets("csvfile").Range(Cells(x, 1), Cells(y, 1)).Cut
Sheets([newname]).Range("a1")
Loop
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Les said:
Please help
This is a few lines of a csv file that I have. I have it
open in an Excel workbook. I need to write/record a macro
that creates a new worksheet and cuts and pastes the data
into them at every line that contains an 'R' on the left
hand side. The amount of rows that contain data after
the 'R' row are variable throughout the file.
Thank you.

H 11/12/2003 08:00:12
R 7926 1601 N B 11/12/2003
11/12/2003 10:15
L BX10A T10 2 18 6
L BX10A T10 5 71 29
L T20 43 63 22
L T21 BM 9 1
L T21 MB 6 1
L T21 11 17 3
L T21 13 14 2
L T21 14 7 2
L T27 87 17 7
L T27 88 19 8
L T30 74 7 7
L T30 76 2 2
L T32 47 20 16
L T33 16 14 13
L T33 19 17 15
L T33 22 3 3
L T38 AT 8 6
L T38 BT 1 1
L T82 73 16 12
L T83 71 2 2
L T83 83 3 1
L T86 12 6 6
R 7927 1494 N H 11/12/2003
11/12/2003 10:20
L T16 301 1 1
L T17 302 6 6
L T18 201 11 11
L T25 204 2 2
L T28 203 31 17
L T30 107 30 22
L T32 304 20 19
L T37 206 27 24
L T37 208 25 20
L T37 209 7 7
L T38 105 71 32
L T41 104 212 60
L T57 205 13 13
L T57 207 7 7
L T81 200 1 1
L T86 300 2 2
L T93 306 104 54
R 7927 1494 N B 11/12/2003
11/12/2003 10:20
L BX10A T10 2 9 6
L BX10A T10 5 35 23
L BX21A T21 20 76 37
Click to expand...
Click to expand...
 
G

Guest

Don
Thanks for the code but I get a type mismatch error in
this line:

x = Application.Match(1, Range("A:A"), -1) - 1

Any ideas?
Les.
-----Original Message-----
When you copy/paste this
After the Cut add a space and underscore
Cut _

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don Guillett said:
Make a backup of your file first and try this.
Assumes that your master worksheet is named csvfile

Sub moveRs()
Do Until Cells(Rows.Count, "a").End(xlUp).Row < 3
x = Application.Match(1, Range("A:A"), -1) - 1
'MsgBox x
y = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox y
newname = Left(Cells(x, 1), 15)
'MsgBox newname
Set NewWs = Worksheets.Add
NewWs.Name = newname
Sheets("csvfile").Select
Sheets("csvfile").Range(Cells(x, 1), Cells(y, 1)).Cut
Sheets([newname]).Range("a1")
Loop
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Please help
This is a few lines of a csv file that I have. I have it
open in an Excel workbook. I need to write/record a macro
that creates a new worksheet and cuts and pastes the data
into them at every line that contains an 'R' on the left
hand side. The amount of rows that contain data after
the 'R' row are variable throughout the file.
Thank you.

H 11/12/2003 08:00:12
R 7926 1601 N B 11/12/2003
11/12/2003 10:15
L BX10A T10 2 18 6
L BX10A T10 5 71 29
L T20 43 63 22
L T21 BM 9 1
L T21 MB 6 1
L T21 11 17 3
L T21 13 14 2
L T21 14 7 2
L T27 87 17 7
L T27 88 19 8
L T30 74 7 7
L T30 76 2 2
L T32 47 20 16
L T33 16 14 13
L T33 19 17 15
L T33 22 3 3
L T38 AT 8 6
L T38 BT 1 1
L T82 73 16 12
L T83 71 2 2
L T83 83 3 1
L T86 12 6 6
R 7927 1494 N H 11/12/2003
11/12/2003 10:20
L T16 301 1 1
L T17 302 6 6
L T18 201 11 11
L T25 204 2 2
L T28 203 31 17
L T30 107 30 22
L T32 304 20 19
L T37 206 27 24
L T37 208 25 20
L T37 209 7 7
L T38 105 71 32
L T41 104 212 60
L T57 205 13 13
L T57 207 7 7
L T81 200 1 1
L T86 300 2 2
L T93 306 104 54
R 7927 1494 N B 11/12/2003
11/12/2003 10:20
L BX10A T10 2 9 6
L BX10A T10 5 35 23
L BX21A T21 20 76 37
Click to expand...
Click to expand...


.
Click to expand...
 
D

Don Guillett

I used what you sent me for testing. That line is looking for a number which
a date is. It is finding the date and subtracting one to get to the line
with the R.Are your dates formatted as text? I was afraid to look for the
"R" since R might be somewhere in your list.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don
Thanks for the code but I get a type mismatch error in
this line:

x = Application.Match(1, Range("A:A"), -1) - 1

Any ideas?
Les.
-----Original Message-----
When you copy/paste this
After the Cut add a space and underscore
Cut _

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don Guillett said:
Make a backup of your file first and try this.
Assumes that your master worksheet is named csvfile

Sub moveRs()
Do Until Cells(Rows.Count, "a").End(xlUp).Row < 3
x = Application.Match(1, Range("A:A"), -1) - 1
'MsgBox x
y = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox y
newname = Left(Cells(x, 1), 15)
'MsgBox newname
Set NewWs = Worksheets.Add
NewWs.Name = newname
Sheets("csvfile").Select
Sheets("csvfile").Range(Cells(x, 1), Cells(y, 1)).Cut
Sheets([newname]).Range("a1")
Loop
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Please help
This is a few lines of a csv file that I have. I have it
open in an Excel workbook. I need to write/record a macro
that creates a new worksheet and cuts and pastes the data
into them at every line that contains an 'R' on the left
hand side. The amount of rows that contain data after
the 'R' row are variable throughout the file.
Thank you.

H 11/12/2003 08:00:12
R 7926 1601 N B 11/12/2003
11/12/2003 10:15
L BX10A T10 2 18 6
L BX10A T10 5 71 29
L T20 43 63 22
L T21 BM 9 1
L T21 MB 6 1
L T21 11 17 3
L T21 13 14 2
L T21 14 7 2
L T27 87 17 7
L T27 88 19 8
L T30 74 7 7
L T30 76 2 2
L T32 47 20 16
L T33 16 14 13
L T33 19 17 15
L T33 22 3 3
L T38 AT 8 6
L T38 BT 1 1
L T82 73 16 12
L T83 71 2 2
L T83 83 3 1
L T86 12 6 6
R 7927 1494 N H 11/12/2003
11/12/2003 10:20
L T16 301 1 1
L T17 302 6 6
L T18 201 11 11
L T25 204 2 2
L T28 203 31 17
L T30 107 30 22
L T32 304 20 19
L T37 206 27 24
L T37 208 25 20
L T37 209 7 7
L T38 105 71 32
L T41 104 212 60
L T57 205 13 13
L T57 207 7 7
L T81 200 1 1
L T86 300 2 2
L T93 306 104 54
R 7927 1494 N B 11/12/2003
11/12/2003 10:20
L BX10A T10 2 9 6
L BX10A T10 5 35 23
L BX21A T21 20 76 37
Click to expand...


.
Click to expand...
Click to expand...
 
L

Les.

Don
Sorry about the reposting, I'm quite new to this. When I
open the csv file in Excel, whether as a csv file or an
Excel worksheet the date is in a cell formatted as a
date. Originally it would have been downloaded from a
website as a csv text file. You are right about the 'R'
it does appear elsewhere in the list.
Les.
-----Original Message-----
I used what you sent me for testing. That line is looking for a number which
a date is. It is finding the date and subtracting one to get to the line
with the R.Are your dates formatted as text? I was afraid to look for the
"R" since R might be somewhere in your list.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don
Thanks for the code but I get a type mismatch error in
this line:

x = Application.Match(1, Range("A:A"), -1) - 1

Any ideas?
Les.
-----Original Message-----
When you copy/paste this
After the Cut add a space and underscore
Cut _

--
Don Guillett
SalesAid Software
(e-mail address removed)
Make a backup of your file first and try this.
Assumes that your master worksheet is named csvfile

Sub moveRs()
Do Until Cells(Rows.Count, "a").End(xlUp).Row < 3
x = Application.Match(1, Range("A:A"), -1) - 1
'MsgBox x
y = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox y
newname = Left(Cells(x, 1), 15)
'MsgBox newname
Set NewWs = Worksheets.Add
NewWs.Name = newname
Sheets("csvfile").Select
Sheets("csvfile").Range(Cells(x, 1), Cells(y, 1)).Cut
Sheets([newname]).Range("a1")
Loop
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Please help
This is a few lines of a csv file that I have. I have it
open in an Excel workbook. I need to write/record
Click to expand...
a
macro
that creates a new worksheet and cuts and pastes
Click to expand...
the
data
into them at every line that contains an 'R' on
Click to expand...
the
left
hand side. The amount of rows that contain data after
the 'R' row are variable throughout the file.
Thank you.

H 11/12/2003 08:00:12
R 7926 1601 N B 11/12/2003
11/12/2003 10:15
L BX10A T10 2 18 6
L BX10A T10 5 71 29
L T20 43 63 22
L T21 BM 9 1
L T21 MB 6 1
L T21 11 17 3
L T21 13 14 2
L T21 14 7 2
L T27 87 17 7
L T27 88 19 8
L T30 74 7 7
L T30 76 2 2
L T32 47 20 16
L T33 16 14 13
L T33 19 17 15
L T33 22 3 3
L T38 AT 8 6
L T38 BT 1 1
L T82 73 16 12
L T83 71 2 2
L T83 83 3 1
L T86 12 6 6
R 7927 1494 N H 11/12/2003
11/12/2003 10:20
L T16 301 1 1
L T17 302 6 6
L T18 201 11 11
L T25 204 2 2
L T28 203 31 17
L T30 107 30 22
L T32 304 20 19
L T37 206 27 24
L T37 208 25 20
L T37 209 7 7
L T38 105 71 32
L T41 104 212 60
L T57 205 13 13
L T57 207 7 7
L T81 200 1 1
L T86 300 2 2
L T93 306 104 54
R 7927 1494 N B 11/12/2003
11/12/2003 10:20
L BX10A T10 2 9 6
L BX10A T10 5 35 23
L BX21A T21 20 76 37






.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Don Guillett

Perhaps you should send me a SMALL workbook sample.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Les. said:
Don
Sorry about the reposting, I'm quite new to this. When I
open the csv file in Excel, whether as a csv file or an
Excel worksheet the date is in a cell formatted as a
date. Originally it would have been downloaded from a
website as a csv text file. You are right about the 'R'
it does appear elsewhere in the list.
Les.
-----Original Message-----
I used what you sent me for testing. That line is looking for a number which
a date is. It is finding the date and subtracting one to get to the line
with the R.Are your dates formatted as text? I was afraid to look for the
"R" since R might be somewhere in your list.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don
Thanks for the code but I get a type mismatch error in
this line:

x = Application.Match(1, Range("A:A"), -1) - 1

Any ideas?
Les.

-----Original Message-----
When you copy/paste this
After the Cut add a space and underscore
Cut _

--
Don Guillett
SalesAid Software
(e-mail address removed)
Make a backup of your file first and try this.
Assumes that your master worksheet is named csvfile

Sub moveRs()
Do Until Cells(Rows.Count, "a").End(xlUp).Row < 3
x = Application.Match(1, Range("A:A"), -1) - 1
'MsgBox x
y = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox y
newname = Left(Cells(x, 1), 15)
'MsgBox newname
Set NewWs = Worksheets.Add
NewWs.Name = newname
Sheets("csvfile").Select
Sheets("csvfile").Range(Cells(x, 1), Cells(y, 1)).Cut
Sheets([newname]).Range("a1")
Loop
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
message
Please help
This is a few lines of a csv file that I have. I
have it
open in an Excel workbook. I need to write/record a
macro
that creates a new worksheet and cuts and pastes the
data
into them at every line that contains an 'R' on the
left
hand side. The amount of rows that contain data after
the 'R' row are variable throughout the file.
Thank you.

H 11/12/2003 08:00:12
R 7926 1601 N B 11/12/2003
11/12/2003 10:15
L BX10A T10 2 18 6
L BX10A T10 5 71 29
L T20 43 63 22
L T21 BM 9 1
L T21 MB 6 1
L T21 11 17 3
L T21 13 14 2
L T21 14 7 2
L T27 87 17 7
L T27 88 19 8
L T30 74 7 7
L T30 76 2 2
L T32 47 20 16
L T33 16 14 13
L T33 19 17 15
L T33 22 3 3
L T38 AT 8 6
L T38 BT 1 1
L T82 73 16 12
L T83 71 2 2
L T83 83 3 1
L T86 12 6 6
R 7927 1494 N H 11/12/2003
11/12/2003 10:20
L T16 301 1 1
L T17 302 6 6
L T18 201 11 11
L T25 204 2 2
L T28 203 31 17
L T30 107 30 22
L T32 304 20 19
L T37 206 27 24
L T37 208 25 20
L T37 209 7 7
L T38 105 71 32
L T41 104 212 60
L T57 205 13 13
L T57 207 7 7
L T81 200 1 1
L T86 300 2 2
L T93 306 104 54
R 7927 1494 N B 11/12/2003
11/12/2003 10:20
L BX10A T10 2 9 6
L BX10A T10 5 35 23
L BX21A T21 20 76 37






.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to create new worksheets from csv file 1
csv import 1
csv import 2
Inputting a value and receiving a result 4
need a formula to transpose sets of data from Sheet1 to Sheet2 7
Sorting by number of laps and completion time. Taking out DNF's. 1
Macro to put info into certain columns based on info in another co 1
Count cells containing specific text when adjacent cell contains datewithin a range... 4

Top