Hello, I have the below code that is copying specific cells from excel files in a directory of subfolders, however each time it moves to a new subfolder, it copies over the data from the files in the previous subfolders. How would I get the code to always find the next empty row in the destination sheet "report" and continually do that until all files have been cycled through in all subfolders.
VBA Code:
Sub Copdata()
Call GetFiles("D:\data\Rev\Analysis\records\files\")
End Sub
Sub GetFiles(ByVal path As String)
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim folder As Object
Set folder = fso.GetFolder(path)
Dim subfolder As Object
Dim file As Object
For Each subfolder In folder.SubFolders
GetFiles (subfolder.path)
Next subfolder
Set destSheet = ActiveWorkbook.Worksheets("Report")
r = 0
For Each file In folder.Files
Set fromWorkbook = Workbooks.Open(file)
With fromWorkbook.Worksheets("Dashboard")
destSheet.Range("A2").Offset(r).Value = .Range("C5").Value
destSheet.Range("B2").Offset(r).Value = .Range("C3").Value
r = r + 1
End With
fromWorkbook.Close savechanges:=False
Next file
Set fso = Nothing
Set folder = Nothing
Set subfolder = Nothing
Set file = Nothing
End Sub
