Public Sub copyAllcsvs(strBookName As String)
Dim strCSVfile As String
'this is necessary since the code is recursive until there is only the original workbook
If Windows.Count = 1 Then GoTo Done
'this takes it to any workbook but the main, essentially the next workbook
Windows.Item(1).ActivateNext
'this ensures the workbook name is not lost
strCSVfile = ActiveWorkbook.Name
'the next two lines select all the sheets and copy them to sheetCompiler.xls (to the end)
Workbooks(strCSVfile).Sheets(ActiveWorkbook.Sheets.Count).Select
Sheets.Copy after:=Workbooks(strBookName).Sheets(Workbooks(strBookName).Sheets.Count)
Sheets.Copy after:=Workbooks(strBookName).Sheets(Workbooks(strBookName).Sheets.Count)
'it now returns to the source workbook and closes the window without altering the book
Workbooks(strCSVfile).Activate
ActiveWindow.Close False
ActiveWindow.Close False
'it now calls itself to work on the remaining collection
copyAllcsvs (strBookName)
Done:Workbooks(strBookName).Sheets(1).Select
End Sub ' copyAllcsvs(strBookName As String)
Public Sub compileSheets()
If Windows.Count = 1 Then
'Checks to ensure there are workbooks open to copy, if none it informs
MsgBox ("No files")
Else
Else
'if there are it calls the compiling subroutine
copyAllcsvs (ActiveWorkbook.Name)
MsgBox "Done"
End If
MsgBox "Done"
End If
End Sub 'compileSheets
Sub fileSelector()'This routine lets you select and open various workbooks and then returns you to the original workbook
Dim strMainWorkbook As String
strMainWorkbook = ActiveWorkbook.Name
Application.FindFile
'After opening the workbooks return to the main workbook to continue processing
Windows(strMainWorkbook).Activate
End Sub 'fileSelector
strMainWorkbook = ActiveWorkbook.Name
Application.FindFile
'After opening the workbooks return to the main workbook to continue processing
Windows(strMainWorkbook).Activate
