Q:

looping through a folder and pulling excel files data into a main sheet and saving a copy after each output is completed

Hi I have written a code in VBA to open certain sheets copy the worksheet data and the  paste the output into another sheet.
I have two questions, the current way I have done this is by writing the excel names in each bit of the code, this would be okay for a few workbooks but there are currently over 50 and the code would be ridiculously long. I was wondering whether there was a way to go through the folder containing the excel files and go through the list until completed.
Sub Openworkbook()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

'first worksheet to copy and paste

Workbooks.Open "C:\"

Set wsCopy = Workbooks("Isabella.xlsx").Worksheets("Sheet2")
  Set wsDest = Workbooks("Testcode1.xlsx").Worksheets("Sheet1")
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
  wsCopy.Range("A1:E86" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)

Workbooks("Isabella.xlsx").Close SaveChanges:=True

'Second worksheet to copy and paste

Workbooks.Open "C:\"

Set wsCopy = Workbooks("Jacob.xlsx").Worksheets("Sheet2")
Set wsDest = Workbooks("Testcode1.xlsx").Worksheets("Sheet1")
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
  wsCopy.Range("A1:E86" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)

Workbooks("Jacob.xlsx").Close SaveChanges:=True

End Sub

Thank you so much for your help.
*********************************UPDATE******************************************
I have rewritten the code below, if you need any help then please let me know and I will do my best to help you.

A:

I ended up going back, getting some sleep and starting the code from scratch and below is what I came up with. Hope this helps
Sub RunIt()
Dim InputBook As Workbook
Dim OutputBook As Workbook
Dim DirFolder As String
Dim DirSel As String
Dim DirFile As String
Dim FileString As String
Dim InputArray As Variant
Dim LastLine As Long
Dim OutputName As String

Set OutputBook = ThisWorkbook

Application.ScreenUpdating = False

' Choose folder and file type (*.* for all files, I have used csv).

DirFolder = "G:\"
DirSel = "*.csv*"
'File output destination
DirOut = "C:\"

' This loops through all the files in the folder above:

    DirFile = Dir(DirFolder & DirSel)
    Do While Len(DirFile) > 0
        FileString = DirFolder & DirFile

        ' I've put in a condition here to pick up only the correct file:
        If Left(DirFile, 5) = "02008" Then
            OutputName = Replace(DirFile, ".csv", "")

            ' Open the file
            Set InputBook = Workbooks.Open(FileString)
              ' Measure the number of rows:
            LastLine = WorksheetFunction.CountIf(InputBook.Sheets(1).Range("A:A"), ">""") + 1
                'Put the data into an array:
            InputArray = InputBook.Sheets(1).Range("A1:L" & LastLine)
               ' Clear out the Lookup tab
            OutputBook.Sheets("Lookup").Range("A1:L1000").ClearContents
               ' Put the array data in:
            OutputBook.Sheets("Lookup").Range("A1:L" & LastLine) = InputArray
               ' Save a copy of the IBS_v022 file
            OutputBook.SaveCopyAs DirOut & "IBS Output " & OutputName & ".xlsm"

            InputBook.Close

        End If

        DirFile = Dir
    Loop

Set InputBook = Nothing
Set OutputBook = Nothing

MsgBox ("Complete")

End Sub
    enter code here

