Dear Ashish,
Thank you so much for your code, for the effort and time dedicated.I am
learning so your answer is much appreciated. I was quite busy recently and
still had no the chance to test it. I will do it tonight. As my knowledge
in VBA is pretty scarce at the moment and I am just in the middle of my
first VBA book can I kindly ask to add some comments to the code so that I
know what each line does. Of course, if that is not a problem for you and
is not much time consuming. Thanks in advance.
Best regards,
Dragomir
On Thursday, April 17, 2014 6:59:53 AM UTC+3, ashish wrote:
>
> Sub merge()
>
> Dim fld As Object, fil As Object, fso As Object, fldpath
> With Application.FileDialog(msoFileDialogFolderPicker)
> .Title = "Choose the folder"
> .Show
> End With
> On Error Resume Next
> fldpath =
> Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
> If fldpath = False Then
> MsgBox "Folder Not Selected"
> Exit Sub
> End If
>
> Set fso = CreateObject("scripting.filesystemobject")
> Set fld = fso.getfolder(fldpath)
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> For Each fil In fld.Files
> If Right(fil.Name, 4) = ".xls" And fil.Name <> ThisWorkbook.Name
> Then Call import_data(fil.Path)
> Next
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
>
> End Sub
>
>
>
> Sub import_data(filename As String)
> Dim wkb As Workbook
> Dim lastrow As Long
> lastrow = ThisWorkbook.Sheets("Sheet1").Range("a65356").End(xlUp).Row
> + 1
> Set wkb = Workbooks.Open(filename)
> wkb.Sheets("Summary Sheet").Range("e2:j2").Copy
> Destination:=ThisWorkbook.Sheets("Sheet1").Range("a" & lastrow)
> wkb.Sheets("Summary Sheet").Range("b13:f13").Copy
> Destination:=ThisWorkbook.Sheets("Sheet1").Range("g" & lastrow)
> wkb.Close
>
> End Sub
>
>
>
> On Thu, Apr 17, 2014 at 1:36 AM, Dragomir Zhelev
> <locomoti...@gmail.com<javascript:>
> > wrote:
>
>> Dear Ashish,
>>
>>
>>
>> Thank you very much for your quick reply. To cut the long way short I am
>> attaching four files: Test1, 2 and 3 and the Test template, which is the
>> master file where I need to consolidate the data from all the workbooks.
>>
>> Yes, all the workbooks will be in the same folder and the Master file
>> (where I will collect the data) as far as I know it is not necessary to be
>> located at the same folder, please, correct me if I am wrong.
>>
>>
>>
>> Here is the exercise I need to perform:
>>
>>
>>
>> I have numerous excel files in a specific folder, all of them with the
>> same number of sheets and all of them containing a sheet called Summary
>> Sheet.
>>
>>
>>
>> In the Summary sheet in the attached workbooks Test 1, 2 and 3 are the
>> two ranges with data colored in red and blue on different rows. I need to
>> copy them and put them in one row in the master file the red first, then
>> the blue. I also need the vba to create a new row automatically for each
>> workbook it copies data from in the master sheet and populate it with the
>> data from the two ranges.
>>
>>
>>
>> As I said in my first post I created a macro that does exactly this
>> operation but just for one file and the file’s name needs to be provided.
>> This is how far I can go with my VBA knowledge at the current stage and I
>> much appreciate your help.
>>
>>
>>
>> Best
>>
>>
>>
>> On Wednesday, April 16, 2014 9:52:01 PM UTC+3, Dragomir Zhelev wrote:
>>>
>>> Dear Excel and VBA lovers,
>>>
>>> This forum is great learning new skills and getting the right knowledge.
>>> As I am going deeper into the VBA coding I have came accross the
>>> following problem which is impossible for me to solve with my current
>>> knowledge and skills in VBA:
>>>
>>> I need to copy a range of cells (one row) from specific sheet, from
>>> numerous closed workbooks in a specific local drive folder and consolidate
>>> the results into a master file (one row for each workbook). I would like to
>>> make excel to create a new row for each worbook from which the data gets
>>> copied in the master file and probably to have the name of the workbook in
>>> the first column in the master file. Below, reading here and exploring
>>> various VBA topics, you can read the code I managed to write myself. It
>>> it tested and it works but the problem is that it is good if you work with
>>> no more than 10 files. The issue is that I need to do the same for
>>> approximately 100 closed workbooks.....
>>>
>>> Sub OpenCopyRange()
>>> Workbooks.Open ("C:\Users\Test1.xlsx"), UpdateLinks:=False
>>> Sheets("Summary Sheet").Select
>>> Range("L4").Select
>>> Range(Selection, Selection.End(xlToRight)).Select
>>> Selection.Copy
>>> Windows("TemplateRange.xlsx").Activate
>>> Range("B3").Select
>>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>> SkipBlanks _
>>> :=False, Transpose:=False
>>>
>>> Windows("Test1.xlsx").Activate
>>> Range("B68").Select
>>> Range(Selection, Selection.End(xlToRight)).Select
>>> Application.CutCopyMode = False
>>> Selection.Copy
>>> Windows("TemplateRange.xlsx").Activate
>>> Range("AU3").Select
>>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>> SkipBlanks _
>>> :=False, Transpose:=False
>>>
>>> Windows("Test1.xlsx").Activate
>>> ActiveWorkbook.Save
>>> ActiveWindow.Close
>>>
>>> End Sub
>>>
>>> If I write the same code for lets say 40 files just changing the name of
>>> the file from which the data needs to be copied and then change the cell
>>> reference where the data needs to be pasted it will still work and will
>>> stil save a lot of time for me, than doing it manually opening each file
>>> and copy the data from it, but from VBA programmer point of you that is
>>> again a waste of time.
>>>
>>> My questions is: Is there a way in which I can ask excel to open all the
>>> closed workbooks within a local drive folder, open a specific sheet in each
>>> workbook and copy a specific range then paste it in the master file where
>>> for each closed workbook there is a new row added in the master file.
>>>
>>> Thank you so much in advance for your help. Any suggestions, examples,
>>> reading materials, advises, etc. are more than welcome.
>>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros...@googlegroups.com <javascript:>.
>> To post to this group, send email to excel-...@googlegroups.com<javascript:>
>> .
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
> --
> *Regards*
>
> *Ashish Koul*
>
>
> *Visit*
> http://www.excelvbamacros.in
> Like Us on
> Facebook<http://www.facebook.com/pages/Excel-VBA-Codes-Macros/151803898222297>
> Join Us on Facebook <http://www.facebook.com/groups/163491717053198/>
>
>
> P Before printing, think about the environment.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
