Combining Employee Data using VBA

 Combining Employee Data from Various Excel files kept in folder using VBA

Problem Statement :- 

    In our Division almost 150 employees works, our Paybill clerk have prepared separate excel for each employee, keeping format same. For some official work we need Basic Pay, D.A., HRA, C.A., CLA, Tribal Allowance, Special Pay etc.

    Data was asked separately for DCPS and GPF employees, also there were separate folders for each category.

Logic :- 

1) Open Folder DCPS/GPF 

2) Open Excel File of each employee

3) Copy the required data and paste in Master excel sheet

Manually it can be done in few hours, but can be done in 1 to 2 minutes, using following code

Option Explicit

Sub preprae_data()

Dim i As Integer

Dim n As Integer

Dim EmpName(1 To 1000) As String

Dim Basic(1 To 1000) As Double

Dim DA(1 To 1000) As Double

Dim HRA(1 To 1000) As Double

Dim CA(1 To 1000) As Double

Dim CLA(1 To 1000) As Double

Dim TBA(1 To 1000) As Double

Dim SpPay(1 To 1000) As Double

Dim path

Dim filename

Dim sheetname(1 To 1000) As String

i = 1

path = ThisWorkbook.path & "\DCPS 21-22\"

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

Do While filename <> ""

On Error Resume Next

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

On Error Resume Next

Worksheets("Statement").Activate

i = i + 1

EmpName(i) = Range("B" & 2)

Basic(i) = Range("C" & 24)

DA(i) = Range("E" & 24)

HRA(i) = Range("f" & 24)

CA(i) = Range("G" & 24)

CLA(i) = Range("H" & 24)

TBA(i) = Range("I" & 24)

SpPay(i) = Range("K" & 24)

sheetname(i) = filename

Workbooks(filename).Close savechanges:=False

filename = Dir()

Loop

Sheets("DCPS").Activate

    

For n = 2 To i + 1

    Range("A" & n) = EmpName(n - 1)

    Range("B" & n) = Basic(n - 1)

    Range("C" & n) = DA(n - 1)

    Range("D" & n) = HRA(n - 1)

    Range("E" & n) = CA(n - 1)

    Range("F" & n) = CLA(n - 1)

    Range("G" & n) = TBA(n - 1)

    Range("H" & n) = SpPay(n - 1)

    

Next n

End Sub

Sub preprae_dataGPF()

Dim i As Integer

Dim n As Integer

Dim EmpName(1 To 1000) As String

Dim Basic(1 To 1000) As Double

Dim DA(1 To 1000) As Double

Dim HRA(1 To 1000) As Double

Dim CA(1 To 1000) As Double

Dim CLA(1 To 1000) As Double

Dim TBA(1 To 1000) As Double

Dim SpPay(1 To 1000) As Double

Dim path

Dim filename

Dim sheetname(1 To 1000) As String

i = 1

path = ThisWorkbook.path & "\GPF 21-22\"

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

Do While filename <> ""

On Error Resume Next

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

On Error Resume Next

Worksheets("Statement").Activate

i = i + 1

EmpName(i) = Range("B" & 2)

Basic(i) = Range("C" & 24)

DA(i) = Range("E" & 24)

HRA(i) = Range("f" & 24)

CA(i) = Range("G" & 24)

CLA(i) = Range("H" & 24)

TBA(i) = Range("I" & 24)

SpPay(i) = Range("K" & 24)

sheetname(i) = filename

Workbooks(filename).Close savechanges:=False

filename = Dir()

Loop

Sheets("GPF").Activate

    

For n = 2 To i + 1

    Range("A" & n) = EmpName(n - 1)

    Range("B" & n) = Basic(n - 1)

    Range("C" & n) = DA(n - 1)

    Range("D" & n) = HRA(n - 1)

    Range("E" & n) = CA(n - 1)

    Range("F" & n) = CLA(n - 1)

    Range("G" & n) = TBA(n - 1)

    Range("H" & n) = SpPay(n - 1)

    

Next n

End Sub