Macro: Post Columns from tabs and summarize it

[jneeley001]

Hello everyone, I'm new to this site and I hope I get to learn VB more. I am developing a template for employee assessments. I've got the macro running to create blank assessment forms with column AI containing the employee's grade. There is no specific number of employees - as new employees get in, a new blank assessment will be created.

I would like to have the data from each employee tab to be summarized in a summary page ("Summary Page" tab). This tab contains a discussion of the criteria (columns A-E) and the corresponding points for each criteria (column F). The data from column AI of each employee tab will be pasted on to this tab (starting from column G onwards) for comparison purposes. So if I have 10 employees, I will have 10 columns with info from column AI of each employee tab (columns G-P). This will be updated as long as there is a new employee assessed (like being refreshed to get the data of the new tabs added).

I am really lost on how to do this summarization . Hope someone could point me in the right direction. Let me know if you need further information.

Thank you in advance!

 

[jneeley001]

By the way, here the VB code I'm using - hope someone can tell me what's wrong with this. Debugger points me to line "Last = LastCol(DestSh)" as the error. Please help! Thanks!

=============

Sub AppendDataAfterLastColumn()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

' Delete the summary worksheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Summary Page").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Add a worksheet with the name "Summary Page"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Summary Page"

' Loop through all worksheets and copy the data to the
' summary worksheet.
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then

' Find the last column with data on the summary
' worksheet.
Last = LastCol(DestSh)

' Fill in the columns that you want to copy.
Set CopyRng = sh.Range("AI:AI")

' Test to see whether there enough rows in the summary
' worksheet to copy all the data.
If Last + CopyRng.Columns.Count > DestSh.Columns.Count Then
MsgBox "There are not enough columns in " & _
"the summary worksheet."
GoTo ExitTheSub
End If

' This statement copies values, formats, and the column width.
CopyRng.Copy
With DestSh.Cells(1, Last + 1)
.PasteSpecial 8 ' Column width
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

=============