Copy_Paste the Data from Multiple Sheets to Master File in Single Cell one after other

[RAKESH230583]

Dear All,

Firstly would like to thanks all friends, who are dedicating their time and sharing their knowledge in such a beautiful forums, Which has helped so many members in sharpening their VBA Skills.

With the help of below quoted VBA sub, I am able to copy (Copy Condition : 1st - To find the pre-defined text from all the excel file [All excel has multiple sheets, Find the text from sheet name "EXP"], available in specified folder available in Desktop, "Folder Name is "dump" ,There are approx. 700 excel files) & paste (Paste Condition: Master file, which has the below sub, and paste the copied data in sheet name, "EXPMAIN"), the data from multiple sheets in a given folder to my Mater file one after other based on Search find results.

Now, Just wanted to check

1) Since the copied data is a range example (Range("A55", "C72)) - Wanted to check whether its possible that the same can be pasted in single cell i.e. (Range("A3")) ?

Thank you once again for all your support.

Sub Copy_Paste()

Application.ScreenUpdating = False

Dim wkbDest As Workbook, wkbSource As Workbook
Dim F As Long, L As Long, R As Long

Const strPath As String = "C:\Desktop\Dump\"

ChDir strPath

strExtension = Dir("*.xls*")

Do While strExtension <> ""

Windows("MasterFile.xlsm").Activate
Sheets("EXP").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = strExtension

    Set wkbSource = Workbooks.Open(strPath & strExtension)

    With wkbSource

F = .Sheets("EXPMAIN").Cells.Find(") NOTES", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
L = .Sheets("EXPMAIN").Cells(Rows.Count, 1).End(xlUp).Row

    End With

Sheets("EXPMAIN").Select
Range("A" & F, "C" & L).Select
Selection.Copy

Windows("MasterFile.xlsm").Activate
Sheets("EXP").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

wkbSource.Close savechanges:=False

strExtension = Dir

Loop
Application.ScreenUpdating = True

End Sub

 

[Yongle]

Do you want all the text from all copied cells concatenated into the destination cell ?

If so
delete these line
Sheets("EXPMAIN").Select
Range("A" & F, "C" & L).Select
Selection.Copy
Windows("MasterFile.xlsm").Activate
Sheets("EXP").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

replace with

Dim c As Range, x As String
For Each c In Sheets("EXPMAIN").Range("A" & F, "C" & L)
    x = x & Chr(10) & cel.Value
Next
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = mystr

TEST on a COPY of your workbook