Find copy and paste into same worksheet

[Clare]

Hi
I am new to the forum and macros. I have a partial macro running. But I would like to have a macro that can find the Words ROI: RECTUM in a long text file imported to excel. The string has 205 rows of data underneath it and 3 columns. The problem is that the subfile for Rectum is located in different cells for every new patient file. I wonder can you help?

Thank you
Clare

[mancubus]

welcome to the forum.

post your workbook and txt files.
and clearly define the desired output.

[Clare]

Hi mancubus, thanks for getting back to me.
Here is the code for the macro I recorded with one specific file. One of the problems is that the Rectum file details with 202 rows of numerical data is not always located in the same cell position. This is the code for this specific patient but I need code to be able to work with many files and be able to pull out the rectum or prostate file details within the larger file of all structures. I imagine if I can get it working for the Rectum I could adjust it for the Bladder or prostate files. These are the three main small files of rougly 206 lines of data within the files of a few thousand lines and three columns . Here is the code. Thanks for any help you can give in advance. Clare


Sub FindROIRectum()
'
' FindROIRectum Macro
' Find Rectum file with 202 rows of data and copy and paste to F20
'


'
Range("B18").Select
ActiveWindow.LargeScroll Down:=1
Range("B45").Select
ActiveWindow.LargeScroll Down:=1
Range("B74").Select
ActiveWindow.LargeScroll Down:=1
Range("B103").Select
ActiveWindow.LargeScroll Down:=1
Range("B132").Select
ActiveWindow.LargeScroll Down:=1
Range("B161").Select
ActiveWindow.LargeScroll Down:=1
Range("B190").Select
ActiveWindow.LargeScroll Down:=1
Range("B219").Select
ActiveWindow.LargeScroll Down:=1
Range("B248").Select
ActiveWindow.LargeScroll Down:=1
Range("B277").Select
ActiveWindow.LargeScroll Down:=1
Range("B306").Select
ActiveWindow.LargeScroll Down:=1
Range("B335").Select
ActiveWindow.LargeScroll Down:=1
Range("B364").Select
ActiveWindow.LargeScroll Down:=1
Range("B393").Select
ActiveWindow.LargeScroll Down:=1
Range("A430:C636").Select
Selection.copy
Range("E425").Select
ActiveWindow.LargeScroll Down:=-1
Range("E396").Select
ActiveWindow.LargeScroll Down:=-1
Range("E367").Select
ActiveWindow.LargeScroll Down:=-1
Range("E338").Select
ActiveWindow.LargeScroll Down:=-1
Range("E309").Select
ActiveWindow.LargeScroll Down:=-1
Range("E280").Select
ActiveWindow.LargeScroll Down:=-1
Range("E251").Select
ActiveWindow.LargeScroll Down:=-1
Range("E222").Select
ActiveWindow.LargeScroll Down:=-1
Range("E193").Select
ActiveWindow.LargeScroll Down:=-1
Range("E164").Select
ActiveWindow.LargeScroll Down:=-1
Range("E135").Select
ActiveWindow.LargeScroll Down:=-1
Range("E106").Select
ActiveWindow.LargeScroll Down:=-1
Range("E77").Select
ActiveWindow.LargeScroll Down:=-1
Range("E48").Select
ActiveWindow.LargeScroll Down:=-1
Range("E19").Select
ActiveWindow.LargeScroll Down:=-1
Range("E20").Select
ActiveSheet.Paste
Range("G23").Select
End Sub





This is a sample of the text file


Dose: PROSTATE



ROI: RECTUM



**************************







Bin
Dose
Volume


1
0
100


2
2.905
100


3
3.195
99.944


4
3.485
99.718


5
3.776
99.358


6
4.066
99.034


7
4.356
98.494


8
4.647
97.879


9
4.937
97.07


10
5.227
96.53


11
5.518
96.071


.......
........



194
58.652
1.017


195
58.942
0.67


196
59.232
0.417


197
59.523
0.262


198
59.813
0.143


199
60.103
0.025


200
60.394
0.001


201
60.684
0.001


202
60.974
0

[Hightree]

When you always lookin to the same rows, why must you go one row down? Please show us a complete sample.

[Clare]

Hello Hightree,
I know very little about VBA macros and that code was created when I recorded a macro. I am sure there is a shorter way to express the moves but I do not know any coding much at all so I cannot add anything further. This is the full code that was generated by the recording.

Thanks for the interest
Clare

[Hightree]

Hello Hightree,
I know very little about VBA macros and that code was created when I recorded a macro. I am sure there is a shorter way to express the moves but I do not know any coding much at all so I cannot add anything further. This is the full code that was generated by the recording.

Thanks for the interest
Clare

Clare you must better describe the goal of your program and post it here

regards,

[Clare]

Hello Hightree,
I have a text file 2000 and sometimes 3000 or more rows long and 3 columns wide which is divided between 17 or less subfile with 202 rows of data stored. I need to search for specific subfiles with headers like "ROI:RECTUM" or "ROI:Bladder" in their heading. I would like to copy the subfile with the 202 rows of data and paste it in F20 on the same sheet. I have to do recalculations on the middle column of the copied file. I would like a neat code to find the "ROI:Rectum" in many different files and repeatedly be able to copy and paste it into F20. I am putting in alternative code here that I also tried and see if this makes anymore sense to you. i get a runtime error 9 with this line
Sheets("Data").Range("F21:H226").ClearContents
Thank you!


Sub finddata()
Dim ROIRectum As String
Dim finalrow As Integer
Dim i As Integer


'clear old search results
Sheets("Data").Range("F21:H226").ClearContents
ROIRectum = Sheets("Data").Range("A13")
finalrow = Sheets("Data").Range("A2090").End(x1Up).Row


For i = 2 To finalrow


If Cells(i, 1) = ROI_Rectum Then Range(Cells(i, 1), Cells(i, 3)).copy
Range("H227").End(x1Up).Offset(1, 3).PasteSpecial x1PasteFormulasAndNumberFormats
End



Next i

End Sub

[Hightree]

Hello Hightree,
I have a text file 2000 and sometimes 3000 or more rows long and 3 columns wide which is divided between 17 or less subfile with 202 rows of data stored. I need to search for specific subfiles with headers like "ROI:RECTUM" or "ROI:Bladder" in their heading. I would like to copy the subfile with the 202 rows of data and paste it in F20 on the same sheet. I have to do recalculations on the middle column of the copied file. I would like a neat code to find the "ROI:Rectum" in many different files and repeatedly be able to copy and paste it into F20. I am putting in alternative code here that I also tried and see if this makes anymore sense to you. i get a runtime error 9 with this line
Sheets("Data").Range("F21:H226").ClearContents
Thank you!


Sub finddata()
Dim ROIRectum As String
Dim finalrow As Integer
Dim i As Integer


'clear old search results
Sheets("Data").Range("F21:H226").ClearContents
ROIRectum = Sheets("Data").Range("A13")
finalrow = Sheets("Data").Range("A2090").End(x1Up).Row


For i = 2 To finalrow


If Cells(i, 1) = ROI_Rectum Then Range(Cells(i, 1), Cells(i, 3)).copy
Range("H227").End(x1Up).Offset(1, 3).PasteSpecial x1PasteFormulasAndNumberFormats
End



Next i

End Sub


Try Sheets(“Data” ).Range(F21:H226).ClearContents or try if this Range can exist

[Hightree]

Try Sheets(“Data” ).Range(F21:H226).ClearContents or try if this Range can exist

Sorry thats wrong tell me with wich version / language you use and submit the sheets

[Clare]

Hi Hightree
I use Excel 2010 VBA to submit the sheets. I really dont need to clearcontents at all. I just need to find copy and paste on the one sheet. Thanks