Data Compilation time reducing Using VBA

            Most of the time data is gathered from Various Sub-divisions, their data is compiled by divisions, Data from Various divisions is then compiled by circle office and similarly data from Circles is combined by Region office then finally by Mantralaya. Google sheet is good option, but Internet connectivity and large size files may sometimes create problems.

Generally, format is kept same, so copy paste is good option but whatever we say most of the time it consumes huge time. Here i have tried it using VBA.

Here i have shown use in following video

VBA Script which executes above works is as Follows, it can be further modified as per requirement. As i will get time i will add explanation line by line for better understanding.

Features of Below script

1) It Opens all the files in a folder

2) Reads the data as specified in code

3) Saves the data in memory

4) Closes the file

5) Opens next file and same procedure again and again

6) After finishing all files it writes data saved in memory on our master workbook.

VBA Script:-

Option Explicit

Sub preprae_data()

Dim i, j As Integer

Dim n As Integer

Dim rows, columns, noofsheets

Dim Col(1 To 500, 1 To 500)

Dim path

Dim filename

Dim sheetname(1 To 1000) As String

Application.ScreenUpdating = False

i = 0

path = ThisWorkbook.path & "\Files\"

filename = Dir(path & "*.xls")

Do While filename <> ""

On Error Resume Next

Workbooks.Open filename:=path & filename, ReadOnly:=True, UpdateLinks:=0

ActiveWorkbook.UpdateLinks = False

On Error Resume Next

For rows = 1 To 50

i = i + 1

For columns = 1 To 20

Col(i, columns) = Sheets(4).Cells(rows, columns)

Next columns

Next rows

On Error Resume Next

sheetname(i) = filename

Workbooks(filename).Close savechanges:=False

filename = Dir()

Loop

For n = 1 To i

For columns = 1 To 20

    Cells(n, columns) = Col(n, columns)

   

Next columns

Next n

Application.ScreenUpdating = True

End Sub