ROBINSON_US
New Member
- Joined
- Feb 27, 2006
- Messages
- 25
Good Morning, I have a directory with 20-30 files (it could change every month) with all the same layout, but different amount of rows. I need to total up two columns, and came up with the following, but it is erroring out on the lRow line. I should mention that I've used this code when totaling up all the worksheets in 1 file, but it doesn't want to seem to work with separate files. Any suggestions?
Thanks
Thanks
Code:
Sub TotalUpReports()
FolderName = "C:\Reports\Monthly Reports"
If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
Fname = Dir(FolderName & "*.xlxs")
Do While Len(Fname)
With Workbooks.Open(FolderName & Fname)
Dim lRow As Long
lRow = .Cells(.Rows.Count,"R").End(xlUp).Row
'title rows are 1 & 2
Cells(lRow + 2, 12).Value = Application.Sum(Range(Cells(3, 12), Cells(lRow, 12)))
Cells(lRow + 2, 18).Value = Application.Sum(Range(Cells(3, 18), Cells(lRow, 18)))
ws.Range("A" & lRow + 2).Value = "TOTALS"
ws.Range("A" & lRow + 2).Font.Bold = True
End With
Fname = Dir
Loop
End Sub
Last edited by a moderator: