2. Home
  3. MS Office
  4. Excel
  5. How to automatically consolidate data from multiple workbooks and worksheets using VBA

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
Loop