VBA copying data from all files in a folder

[bhbhbh]

Hi everyone,

I have written a small piece of code but I am having trouble finishing it off.

Below my code opens all the files in a folder, selects a sheet and copies a range of cells which it then pastes to a master spreadsheet.
After I have pasted the values to the master spreadsheet I need to return to the file (from where I copied the data), close it (don’t have to save this one) and then move on to the next file i.e. Worksheets (q)

The code is fine for the first sheet, but then I get an error when It comes to the next file. I hope you are able to help me out with this. It would be a lifesaver.

I am using excel 2016 on a windows 7 machine.



Sub LoopThroughDirectorytoEdit()
Dim MyFile As String
Dim Filepath As String
Dim q As Long

Filepath = "NAME"
MyFile = Dir(Filepath)

Do While Len(MyFile) > 0

If MyFile = "bookz.xlsm" Then
Exit Sub
End If
Workbooks.Open (Filepath & MyFile)
For q = 1 To Application.Worksheets.Count
Worksheets(q).Activate

Sheets("Log").Select
Range("A2:F1000").Select
Selection.Copy
Windows("MASTER SPREADSHEET NAME").Activate
Sheets("AllActivity").Select
'Finds empty row'
NextRow = Sheets("AllActivity").Range("A" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Paste


Next q
ActiveWorkbook.Save
ActiveWorkbook.Close
MyFile = Dir
Loop
End Sub