Copy, paste data from one sheet to another based on specific text

[Chandresh]

I am trying to write a macro that can open the file from the folder and search for a specific text and copy all the data below that text to another macro workbook and in a specific sheet. Since the number of rows is not constant I am unable to copy that. Could you please help?

Pointers
*CAIFL if the workbook from data has to be copied to Try.xlsm in sheet “ABC”
The text to search is “GAINS/LOSS” in column A
Data to copy – All the data below “GAINS/LOSS” to Try.xlsm in sheet “ABC”
(For example, if GAINS/LOSS is in cell A18 then - copy A19 to F column last cell)

Sub CAIFL()
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.CutCopyMode = False
Dim Path As String
Dim Filename As String
Dim Sheet As Worksheet
Path = ThisWorkbook.Path & "\Old Report\"
Filename = Dir(Path & "*CAIFL.xlsx")
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

' For searching the text in column A
Worksheets("Sheet1").Select
Range("A1").Select
Cells.Find(What:="GAINS/LOSS", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select

'For Copyig the lines
Range("A19:F101").Select
Selection.Copy
'For pasting data in Macro file
Windows("Try.xlsm").Activate
Worksheets("ABC").Select
Range("I15").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Filename).Close
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.CutCopyMode = True
End Sub

 

[OaklandJim]

It seems that you are asking for a lot.

I can't promise that I can do this but I'll try. If not me then possibly other people who are smarter about Excel.

If you want assistance you are more likely to get help if you help the helper so they do not need to guess about or recreate your data.

If the data is not confidential post a link to your workbooks. If necessary you can enter fake-but-realistic data before providing the link. Put the files on Dropbox, Box, 1Drive, Google Drive etc. Use the link icon above the message area. Make sure that other people can access the file!

Or, consider sharing relevant data using Mr Excel's excellent XL2BB addin that enables you to post a portion of a worksheet. See XL2BB - Excel Range to BBCode for details.