Extracting Data from Various Excel File to Master Sheet

[violetserrao]

Hello,

I request you help on the spreadheets. I have filled apprisal forms for each employees i.e. around 2000 employees. Each employees spreadsheet has two tabs 1) Self Evalaution 2) Manager Evalaution. I need to get the data from the two tabs to a master file.

I am attaching the filled forms and a master sheet.

I need the follwing data to be extracted from the tabs

Self Evalaution Tab:-

Employee Name:
Employee No. :
Employee's Calculated Rating:
Employee's Self Rating:
Accomplishments:
Strengths:
Areas for Improvement:
Development Activities:
Comments:

B]Manager Evalaution Tab:-[/B]

Manager's Calculated Rating:
Manager's Rating of Employee:
Accomplishments:
Strengths:
Areas for Improvement:
Development Activities:
Comments:

Thanks & Regards

Violet

[JBeaucaire]

'WORKBOOKS TO 1 SHEET
I have a macro for collecting data from all files in a specific folder, published here:

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.

For instance the sample code uses Master as the sheetname being copied into, you'll have to change that to Master Sheet in the code.

fPath refers to where on your hard drive the files are stored.
fName = Dir("*.xls") is the filter for what files to open, this filter will open every .xls file in the fPath.


As for the "this is the section to customize", here's some code to get you started...it gets the first few values from each tab for you, expand it with more lines of code to get the rest to complete it on your own.

        'This is the section to customize, replace with your own action code as needed
            With wbkNew.Sheets("Master Sheet")
            'Name
                .Range("A" & NR).Value = Sheets("Self Evaluation").Range("B9").Value
            'Employee Number
                .Range("B" & NR).Value = Sheets("Self Evaluation").Range("E9").Value
            'Emp Calculated Rating
                .Range("C" & NR).Value = Sheets("Self Evaluation").Range("D234").Value
            'etc...keep adding lines for each item you want
            
        
        End With
        
        'close file
            wbData.Close False

[violetserrao]

Hi

I used the follwoing code - it shows an error "Run-time error 9, Subscript out of range"



Sub Consolidate()
'Author: Jerry Beaucaire'
'Date: 9/15/2009 (2007 compatible)
'Summary: Open all Excel files in a specific folder and merge data
' into one master sheet (stacked)
' Moves imported files into another folder
Dim fName As String, fPath As String, fPathDone As String, OldDir As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wbkNew As Workbook

'Setup
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set wbkNew = ThisWorkbook
wbkNew.Activate
Sheets("Master").Activate 'sheet report is built into

If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub

If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
Cells.Clear
NR = 1
Else
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
End If

'Path and filename (edit this section to suit)
fPath = "D:\Violet\Lavina\" 'remember final \ in this string
fPathDone = fPath & "Imported\" 'remember final \ in this string
On Error Resume Next
MkDir fPathDone 'creates the completed folder if missing
On Error GoTo 0
OldDir = CurDir 'memorizes the users current working path
ChDir fPath 'activate the filepath with files to import
fName = Dir("*.xls") 'listing of desired files, edit filter as desired

'Import a sheet from found file
Do While Len(fName) > 0
If fName <> wbkNew.Name Then 'make sure this file isn't accidentally reopened
'Open file
Set wbData = Workbooks.Open(fName)

'This is the section to customize, replace with your own action code as needed
With wbkNew.Sheets("Master")
'Employee Name
.Range("A" & NR).Value = Sheets("Self Evaluation").Range("B9").Value
'Employee Number
.Range("B" & NR).Value = Sheets("Self Evaluation").Range("E9").Value
'Emp Calculated Rating
.Range("C" & NR).Value = Sheets("Self Evaluation").Range("D234").Value
'etc...keep adding lines for each item you want
End With
'close file
wbData.Close False
'Next row
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
'move file to Imported folder
Name fPath & fName As fPathDone & fName
'ready next filename
fName = Dir
End If
Loop

ErrorExit: 'Cleanup
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True 'turn system alerts back on
Application.EnableEvents = True 'turn other macros back on
Application.ScreenUpdating = True 'refreshes the screen
ChDir OldDir 'restores users original working path
End Sub

[JBeaucaire]

1) Please edit your post above and add proper code tags as shown in my posts.

2) When the error occurs and you DEBUG, what line of code is highlighted?
What variables are in that line of code and what values are in those variables? (hover mouse over variables to see values)

3) Did you edit the post above and put in code tags? Do that before responding, please. Thanks.

Be sure to read through the Forum Rules so you can use and follow them effectively.

[violetserrao]

Hello,

I am very sorry....but I am unable to manager the codes...I am new to these codes.

I tried as instructed by you.

All my files are in the directory - D:\2011\

(I am attaching the forms)

My master file is named as Master.xls (where I have added the code)

Thanks
Violet

[JBeaucaire]

Please read post #4 again and follow the instructions in step 1) and repeated for emphasis in step 3). It was repeated to stress how important it was and you still ignored it. Then post here again to call me back.

[violetserrao]

Sorry... for keepig you bothered, please let me know, if this will work on 2003 excel version?.

As I tried my best, but unable to get to it.

Thanks

[JBeaucaire]

Please read post #4 again and follow the instructions in step 1) and repeated for emphasis in step 3). It was repeated to stress how important it was and you still ignored it. Then post here again to call me back.