Can you please let us know if all files saved in a single folder or in
multiple folders?
Can you please share some dummy files and master file how the output will look
like
Thanks
Ashish
Sent on my BlackBerry® from Vodafone
-----Original Message-----
From: Dragomir Zhelev <locomotivplov...@gmail.com>
Sender: excel-macros@googlegroups.com
Date: Wed, 16 Apr 2014 11:52:01
To: <excel-macros@googlegroups.com>
Reply-To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Macro to copy data from a specif range from many
closed workbooks in a folder to a master file creat
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+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.
--
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.
