I think I'd skip the part about reading the "raw" data into the "Paste" Sheet.
Instead, I'd read from the text file and place it in the appropriate sheet.
A simple method would be like this:
Option Explicit
Sub ReadData()
Dim TDataFile, TextLine, TxtArray
Dim Sht, oRow, inx
TDataFile = "C:\temp\barber.txt"
Sht = 1
oRow = 1
' Open file -
Open TDataFile For Input As #1
' Loop until end of file --
Do While Not EOF(1)
' Read line into variable
Line Input #1, TextLine
'---split line using "," delimeter--
If (UCase(Left(TextLine, 6)) = "TOTAL:") Then
Sht = Sht + 1
oRow = 1
Else
TxtArray = Split(TextLine, Chr(9))
oRow = oRow + 1
For inx = 0 To UBound(TxtArray)
Sheets(Sht).Cells(oRow, inx + 1).Value = TxtArray(inx)
Next inx
End If
Loop
Close #1 ' Close file.
End Sub
this assumes a couple of things.
The destination sheets are in the first three sheets
(if the first destination sheet is sheet2,then change the starting value of
oRow)
Next, it assumes the data is tab delimited (chr(9)) if you are using a
different
delimiter, thenchange the "Split" line.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: JsinSk
To: excel-macros@googlegroups.com
Sent: Fri, July 13, 2012 10:13:32 AM
Subject: $$Excel-Macros$$ Search for keyword, and move all rows above found
keyword to specific sheet
Hello Group,
I'm attempting to automatically format a report that has several parts and
break
it down to what I need.
The report comes in plain text and has three parts that all in with the phrase
"total :" in the last part of that section. I'd like to see about having it
break down each section into different sheets. I currently have it broken down
to four sheets.
Sheet 1 "Paste Sheet" - raw data goes here
Sheet 2 "Listing" - First section of the raw data is put here
Sheet 3 "Bond" - Second section of raw data
Sheet 4 "Clear" - Third section
The way I currently do the is is search for "total :" scroll up to the top of
the paste sheet until I get to the top row and cut all data and move to the
first sheet. Delete blank rows still the next section and repeat searching for
total :. Then I do some text to columns to format the data to my requirements.
All the formatting is done already just need help getting the data to
automatically move to different sheets.
Uploading sample data could be done if needed, will just require quite a bit of
time to cleanse confidential information.
Regards,
Jason--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners
and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com