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 <jsin...@gmail.com> 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
