• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merging worksheets from different workbooks - values only

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:

TOTALS:TOTAL Data 1TOTAL Data 2TOTAL Data 3TOTAL Data 4TOTAL Data 5TOTAL Data 6TOTAL Data 7TOTAL Data 8
(LINE 12) File 1DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 2DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 3DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 4DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 5DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 6DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 7DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 8DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 9DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 10DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 11DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 12DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 13DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 14DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 8
(LINE 12) File 15DATA 1DATA 2DATA 3DATA 4DATA 5DATA 6DATA 7DATA 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
Thank you!!! Jas
 
Last edited by a moderator:

Marc L

Excel Ninja
Hi !​
A sample of scanning a folder and its sub folders in this thread :
 
Top