0

I have multiple excel files in folder AA with this common specs: - In sheet 1, cell N10 has a specific date - In sheet 2, the range D10:F49 has data, but the last, bottom, cells in this range may be empty

In an existing worksheet i need to copy the specific date in the first column, and in the next three columns the cells in the range that contain data. The date has to be in every row relative to the data range.

For example,

I have this:

enter image description here

And I need this:

enter image description here

In the images you can see an example with data of 1 file, but I need it for several... I need this worksheet to update when I save new files in the AA folder. Can this be done?

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
jcsp2000
  • 1
  • 1
  • 2
    Welcome to SO! Since the only question is asked is **Can this be done?** - the answer is yes! Please read [How to Ask](http://stackoverflow.com/help/how-to-ask) to receive the most effective help on SO. – Scott Holtzman Mar 07 '16 at 15:49
  • These look like identical pictures – Davesexcel Mar 07 '16 at 16:12
  • Please look at the cell names. Pic 1 is the data contained in ONE file, of many, from the AA Folder (N10 is from another sheet). Pic 2 is how that particular info from that one file, should be called for by the summary file. – jcsp2000 Mar 07 '16 at 17:52

1 Answers1

0

Just set your ranges and workbooks.

Dim wkbk1 As Workbook
Dim wkbk2 As Workbook

' be sure to add the path if the workbook isn't in your working directory
Set wkbk1 = workbooks("Workbook1.xlsx")
Set wkbk2 = workbooks("Workbook2.xlsx")  

Dim range1 As Range
Dim Range2 As Range

Set Range1 = wkbk1.Range("A1:B234") 'or whatever
Set Range2 = wkbk2.Range("A1:B234")  ' you should get the picture by now...

Need a data array? try this.. super easy.

Dim data As variant
data = Range1

OK now that you have your data declared, reference this post for looping in a directory full of spreadsheets... Loop through files in a folder using VBA?

you should now have the tools to get going. Add a comment if you'd like more help.

Community
  • 1
  • 1
Zombro
  • 311
  • 1
  • 9