Compiling Data from Multiple Stardardized Excel Files into ONE Excel Master File

This workbook should do it. It will need some customization from your part.

It will also only work with the file name you provided us with. You can improve it by having a variable bear the file name. You can google that, it should be fairly easy. You can also improve it by having it close the file after use, improve format or make it more efficient with a leaner code.

To enter another file, press the commandbutton again, the new set of data will be added in the next row. You can also run a batch of files, but that's more complicated.

Hope this helps.

Winch

Hi Ammr,

Where are the files to be compiled? Are they in different books, different sheets, or several per sheet in various books?????

Here's how I would start your program: (the OCR sheet must be activated)

Sub Akurdi(): Dim i As Integer, j As Integer, r As Long: j = 0
Dim Name As String, ID As String, Major As String, Course As String, Section As Integer
Dim Term As Integer, PType As String, PForm As String, PDate As Date, No(15) As Integer
With ActiveWorkbook.Worksheets("Sheet1")
.Cells(1, 1) = "Name": .Cells(1, 2) = "ID": .Cells(1, 3) = "Major"
.Cells(1, 4) = "Course": .Cells(1, 5) = "Section": .Cells(1, 6) = "Term"
.Cells(1, 7) = "PType": .Cells(1, 8) = "PForm": .Cells(1, 9) = "PDate"
For i = 1 To 14: .Cells(1, i + 9) = "No. " & i: Next i
End With

Name = Cells(11, 3): ID = Cells(12, 3): Major = Cells(13, 3)
Course = Cells(14, 3): Section = Cells(15, 3): Term = Cells(16, 3)
PType = Cells(17, 3): PForm = Cells(18, 3): PDate = Cells(19, 3)
For i = 22 To 61 Step 3: j = j + 1: No(j) = Cells(i, 5): Next i

With ActiveWorkbook.Worksheets("Sheet1")
r = .Range("A" & Rows.Count).End(xlUp).row + 1
.Cells(r, 1) = Name: .Cells(r, 2) = ID: .Cells(r, 3) = Major
.Cells(r, 4) = Course: .Cells(r, 5) = Section: .Cells(r, 6) = Term
.Cells(r, 7) = PType: .Cells(r, 8) = PForm: .Cells(r, 9) = PDate
For i = 1 To 14: .Cells(r, i + 9) = No(i): Next i
End With

End Sub

To get it to run I had to change the Year - I changed it to 2011!!!

AND, I inserted a Worksheet in your book!

Directions for running the routine(s) just supplied in a module

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” or “Sheet” as directed above from the Insert menu

Type "Option Explicit" then paste the code into the white space on the right

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

To run the routine you need the Oral Communication Rubric "up" - I guess that you inserted a Worksheet, or it wouldn't have run at all. But is it one book one rubric and are the Worksheets all named "Oral Communications Rubric"??? - I'll proceed on that assumption; with a technique known as "running the directory".

To run the routine you need the Oral Communication Rubric "up" - I guess that you inserted a Worksheet, or it wouldn't have run at all. But is it one book one rubric and are the Worksheets all named "Oral Communications Rubric"??? - I'll proceed on that assumption; with a technique known as "running the directory".

This is set up so that the book that you're collecting the data in is in the same folder as the others and has a worksheet named "Sheet1"

Sub Akurdi(): Dim wb As Workbook, ws As Worksheet, wd As Worksheet, N As String
Dim i As Integer, j As Integer, r As Long: j = 0
Dim Name As String, ID As String, Major As String, Course As String, Section As Integer
Dim Term As Integer, PType As String, PForm As String, PDate As Date, No(15) As Integer
Dim S As String, P As String, U As String: P = ActiveWorkbook.Path & "\": U = Dir(P)

Set ws = ActiveWorkbook.Worksheets("Sheet1"): N = ActiveWorkbook.Name
With ws
.Cells(1, 1) = "Name": .Cells(1, 2) = "ID": .Cells(1, 3) = "Major"
.Cells(1, 4) = "Course": .Cells(1, 5) = "Section": .Cells(1, 6) = "Term"
.Cells(1, 7) = "PType": .Cells(1, 8) = "PForm": .Cells(1, 9) = "PDate"
For i = 1 To 14: .Cells(1, i + 9) = "No. " & i: Next i
End With

SetNextBook:
If U = N Then GoTo GetNextBook  'you don't need this line unless you're not using one of the source books

Workbooks.Open fileName:=P & U
    Set wb = ActiveWorkbook: Set wd = wb.Worksheets("Oral Communications Rubric")
With wd
Name = .Cells(11, 3): ID = .Cells(12, 3): Major = .Cells(13, 3)
Course = .Cells(14, 3): Section = .Cells(15, 3): Term = .Cells(16, 3)
PType = .Cells(17, 3): PForm = .Cells(18, 3): PDate = .Cells(19, 3)
For i = 22 To 61 Step 3: j = j + 1: No(j) = .Cells(i, 5): Next i
End With

With ws
r = .Range("A" & Rows.Count).End(xlUp).row + 1
.Cells(r, 1) = Name: .Cells(r, 2) = ID: .Cells(r, 3) = Major
.Cells(r, 4) = Course: .Cells(r, 5) = Section: .Cells(r, 6) = Term
.Cells(r, 7) = PType: .Cells(r, 8) = PForm: .Cells(r, 9) = PDate
For i = 1 To 14: .Cells(r, i + 9) = No(i): Next i
End With
            wb.Close SaveChanges:=False
GetNextBook:
U = Dir(): GoTo SetNextBook
EndSub: End Sub

If you're going to run it from one of the source books then you need to get rid of that bypass line that I commented on. As long as you have the client book in the same folder then the path should take care of itself!

Yes - put the code in a module in the client book - you can't insert a sheet from the VBE, but you can click on a sheet in the VBE - but we're not using any sheet code here.

Alternate code for the path "P" would be something like this;

P = "C:\Documents and Settings\User\"