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

Chandresh

Board Regular
Joined
Jul 21, 2009
Messages
146
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0

Forum statistics

Threads
1,223,375
Messages
6,171,735
Members
452,419
Latest member
mapa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top