RAKESH230583
New Member
- Joined
- Jan 10, 2011
- Messages
- 46
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.
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.
VBA Code:
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