Apply a macro across a hundred workbooks, and then copying the results into a master Excel

[icomefromchaos]

So far the code loops through all of the files, but only copies sheet 1 instead of making the combined sheet. It also creates hundreds of blank sheets in my new workbook that only has 1 combined tab that keeps looping the same information from the first file in my directory.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Sub BatchProcessing()
MyPath = "C:\Users\USERNAME\Desktop\OCCREPORTS\Files\"
MyTemplate = "*.xls*"  ' Set the template.
MyName = Dir(MyPath & MyTemplate)    'Retrieve the first file
Do While MyName <> ""
    Workbooks.Open MyPath & MyName
    Combine                 'do your thing
    Workbooks(MyName).Close         'close
    MyName = Dir                    'Get next file
Loop
End Sub
Sub Combine()
Dim J As Integer
Dim s As Worksheet
Dim LastCol As Integer
    
       
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"




For Each s In ActiveWorkbook.Sheets
        If s.Name <> "Combined" Then
            Application.Goto Sheets(s.Name).[A1]
            Selection.CurrentRegion.Select
            Sheet.UsedRange.Clear
            LastCol = Sheets("Combined").Cells(1, Columns.Count).End(xlToLeft).Column
            Selection.Copy Destination:=Sheets("Combined"). _
            Cells(1, LastCol + 1)
        End If
    Next
End Sub
</code>

The end Goal of this Macro is to copy all of the information that is in all of my workbooks, copy it to a tab that is called 'Combined' and then copy the 'Combined' sheet and then place it in my Target workbook. The Target workbook will house all of the information from 300 workbooks in one tab. For example, Workbook 1 has Sheet 1, sheet 2... sheets 40The selection of code below combines sheets 1...40 into a new sheet called 'Combined'

 'Selection.CurrentRegion.Select   
 'Sheet.UsedRange.Clear    
 'LastCol = Sheets("Combined").Cells(1, Columns.Count).End(xlToLeft).Column                    
 'Selection.Copy Destination:=Sheets("Combined"). _                              
 'Cells(1, LastCol + 1)From there I need to read the other workbooks, loop the code above and then paste the combined sheet into Sheet 1 of the Target workbook.

 

[icomefromchaos]

Here is a zoomed in example: https://drive.google.com/file/d/0B0m5F-NRHk_kM3FoUHdlblBUbUE/view?usp=sharing

 

[icomefromchaos]

I think what the logic issue is, is that it is not starting on the next free row from the last workbook

For example:

Pasting first entry from workbook1 into Target: Column A starts at row 1, and paste information down to row 5, Column B paste information from B:1 to B:10
Pasting second entry from workbook2 into Target: Paste info starts at A:6, and for column B:11.

This gives the stair case effect to my table.

Instead when pasting the last workbook it needs to know that column B is the has the most rows occupied, so everything between A:1-10 can not contain any data, so the next workbook needs to start on A:11 and then begin pasting the values.

 

[RickXL]

Looking at your zoomed output, not all the workbooks have the same format.

In the example you give, one wkeksheet has a column labelled "HasClaims" and the other finishes at "HasDwell". If the columns are not the same in all workbooks then how is it going to work?

 

[icomefromchaos]

I was looking at it last night, and it seems that there are always by default the same number of columns, unless there is a unique error, and then it is added to the excel document and then e-mailed to me. So, I was thinking, if it would be possible to calculate the total number of columns in all of the sheets, and use that as a master template, and then, when you have several workbooks that don't contain that field, it would automatically populate a 'False' or 'Null'. Or Perhaps I could get into contact the manufacture, and have them give me a master key of all of the possible columns and in what order they are presented... Using that as a template, if the current excel workbooks that I have don't contain a specific field then it would populate a False / Null.

 

[RickXL]

Hi,

You need to think about what you are trying to do. Obviously, the rows do not match between worksheets so is it a good idea to combine them into one? You will have to read them separately anyway.

If columns can vary between worksheets, will the columns still align or could there be one variable in column 3 on one worksheet and a different variable in column 3 on another?

If you can obtain a "master" layout that includes all the columns then when the data is copied in you will need to match the columns at the same time. A straight copy of the block of data will not be possible. It will need to be column by column.