How to automatically consolidate data from multiple workbooks and worksheets using VBA

Sometimes we have a task to go through a number of files and make some changes in each of them or pull data from them. In this article we will have a look at the Excel-VBA code which allows to do all of that.

One of the methods to open all files in a folder one by one is using cycle while:

sFolder = "C:/Folder/"
sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator)
sFiles = Dir(sFolder & "*.xls*")

Do While sFiles <> ""
Workbooks.Open sFolder & sFiles

'what to do in each file

ActiveWorkbook.Close False

'write data

sFiles = Dir