Move to next empty row when copying from next subfolder

Orfevre

New Member
Joined
Jul 11, 2022
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
 
Did you get that error with your original code?

Also is the report sheet in the same workbook as the code?
No didnt get that error in original code, it runs fully just when it goes onto a new folder it pastes over things already in the report sheet that it copied from previous folders files and yes the report sheet is in the workbook with the code.
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
In that case use
VBA Code:
Set destSheet = ThisWorkbook.Worksheets("Report")
 
Upvote 0
In that case you do not have a sheet called "Report" in the workbook that contains the code.
 
Upvote 0
In that case you do not have a sheet called "Report" in the workbook that contains the code.
thank you for your time and patience, there was a space after the sheet name in excel.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top