jassybun
Member
Hello friends!
I am trying to combine the line 12 on the first page of multiple workbooks in multiple folders into one worksheet, so I can total the data from line 12 of each initial summary page.
Also, I need the values only. The formula in A12 is ridiculous I don't know why they set it up that way, but it pulls the name of the file as info type instead of just them typing it out so I have to avoid that as 12A indicates what area the data is for.
Would it be possible to go into the subfolders as well? I am yanking them all out and saving them in a folder, but if that can be avoided as there are 10 folders at least with 3-10 files inside them, and this is a weekly task, and right now I have to backtrack 4 weeks worth of data. Any help would greatly be appreciated.
So it should end up something like this:
This works but I still have to yank all the files out from the folders. Then I still have to go inside each file and into the first tab, copy line 12 and then paste them into the another worksheet to then be added.
Thank you!!! Jas
I am trying to combine the line 12 on the first page of multiple workbooks in multiple folders into one worksheet, so I can total the data from line 12 of each initial summary page.
Also, I need the values only. The formula in A12 is ridiculous I don't know why they set it up that way, but it pulls the name of the file as info type instead of just them typing it out so I have to avoid that as 12A indicates what area the data is for.
Would it be possible to go into the subfolders as well? I am yanking them all out and saving them in a folder, but if that can be avoided as there are 10 folders at least with 3-10 files inside them, and this is a weekly task, and right now I have to backtrack 4 weeks worth of data. Any help would greatly be appreciated.
So it should end up something like this:
| TOTALS: | TOTAL Data 1 | TOTAL Data 2 | TOTAL Data 3 | TOTAL Data 4 | TOTAL Data 5 | TOTAL Data 6 | TOTAL Data 7 | TOTAL Data 8 |
| (LINE 12) File 1 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 2 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 3 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 4 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 5 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 6 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 7 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 8 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 9 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 10 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 11 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 12 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 13 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 14 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
| (LINE 12) File 15 | DATA 1 | DATA 2 | DATA 3 | DATA 4 | DATA 5 | DATA 6 | DATA 7 | DATA 8 |
This works but I still have to yank all the files out from the folders. Then I still have to go inside each file and into the first tab, copy line 12 and then paste them into the another worksheet to then be added.
Code:
Option Explicit
Sub GetSheets()
Dim Path As String, fileName As String
Dim Sht As Worksheet
Path = "\Users\myname\Desktop\DOK420\"
fileName = Dir(Path & "*.xls")
Do While fileName <> ""
Workbooks.Open fileName:=Path & fileName, ReadOnly:=True
With ActiveWorkbook
.Worksheets(1).Copy After:=ThisWorkbook.Sheets(1)
ThisWorkbook.Sheets(2).Name = .Name
End With
ActiveWorkbook.Close
fileName = Dir()
Loop
End Sub
Last edited by a moderator:
