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

'

The Sub function reads all 300 workbooks that need to have the DoWork function executed 
Sub Batch()
Dim wb As Workbook, MyPath, MyTemplate, MyName
Dim Filename, Pathname As String

Pathname = ActiveWorkbook.Path & "\Files\" 							' Reads in directory that contains files that need to be edited by DoWork
Filename = Dir(Pathname & "*.xls")
Do While Filename <> "" 
    Set wb = Workbooks.Open(Pathname & Filename) 				' Opens files in "Files" 
    DoWork wb 											' Executes function DoWork
    wb.Close SaveChanges:=True 								' closes and saves changes 
    Filename = Dir()
	
Loop 												' loops 
End Sub																	' What this Sub Batch() does not do properly... It opens the Target workbook, but it does not copy and save the Combined tab from the other workbooks.	
Sub DoWork(wb As Workbook) 							' Beginning of DoWork: this function merges all of the sheets that are contained in a workbooks into one sheet and then TRIES to copy the information from the new 'Combined' sheet into Target workbook Sheet 1
    With wb 
        Dim J As Integer
        Dim s As Worksheet
        Dim NextEmptyCol As Long

    On Error Resume Next							' If error ties to go to next tab
    Sheets(1).Select 								' Selects sheet
    Worksheets.Add ' add a sheet in first place		' Adds a new sheet 
    Sheets(1).Name = "Combined"						' Renames Sheet 1 and calls it "Combined"

For Each s In ActiveWorkbook.Sheets					' Begins For Loop for all active workbooks
        If s.Name <> "Combined" Then 				' If the Sheet "Combined was created continues
            Application.Goto Sheets(s.Name).[A1]	' Starts reading at A1
            Selection.CurrentRegion.Select
            Sheet.UsedRange.Clear
            LastCol = Sheets("Combined").Cells(1, Columns.Count).End(xlToLeft).Column 					 ' finds all columns in use
            Selection.Copy Destination:=Sheets("Combined"). _ 											 ' copies all columns 
            Cells(1, LastCol + 1) 																		 ' Adds all copied columns  from sheets 1-N to "Combined" sheet where all copied information is contained in a table like format.
            ThisWorkbook.Sheets("Combined").Copy														 ' Here and below.... I am trying to copy all of the information that was opened in my original workbook and combine it my Final workbook called 'Target' in Sheet 1
            Application.Goto Sheets(s.Name).[A1]
            Selection.CurrentRegion.Select
            Sheet.UsedRange.Clear
            ActiveWorkbook.SaveAs "C:\Users\username\Desktop\OCCREPORTS\Target.xlsx", FileFormat:=51 	' Saves information that was pulled from the Combined tab and saves it in my Target .... I also changed username... 
       End If
    Next
End With
End Sub

' *Notes 1*
' 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 40
' The 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)

-------
'After this has taken place, I want to have the information from Combined saved into a new workbook in sheet called Sheet 1
'and then loop through all 300 workbooks, making the combine tab, coping the combine tab, and then saving it into target in the next free ROW.
'Saving in the next free row will allow my data to remain uniform.

-------
' *Notes 2*
' Here is the code that I used to to test to make sure that my DoWork function is actually creating the Combined sheet and copying my information over correctly...
' I've tried to create the Sub Batch() around this part...

'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

-----
Notes 3*
So basically apply the function in Note 2 across all 300 of my workbooks and then copying the 'Combined' sheets from all 300 books and paste it all into one Sheet in Workbook Target.

Thank you for your help; I've invested quite a bit of time into this, and I hope my documentation has helped you understand what I'm trying to accomplish.

Note 4: Right now, the code opens Target workbook, copies my sheets VINCases, VINdata , Data, ... but does not Combine the sheets. Then after running through 1 workbook, it closes the previous, but then loops through creating blank sheets Sheet1, sheet2, sheet3... etc. until the macro loops through all of the workbooks in my \Files\ folder.

This works great! ... there is one small error. When combining the tabs from workbooks 1-100, it combines the tabs by next column. This is great. However, when it copies to Target, it keeps this formatting. What would make this perfect is that I need to copy the information from the combined tab, and then append the data by next row in the Target workbook... This will allow my data to be represented in a massive table.

Thank you for your help

I have made an example data set that better explains that I'm trying to accomplish.

https://drive.google.com/folderview?...kk&usp=sharing