Macro to Convert Data to Different Format

[pcook911]

I have a spreadsheet with three tabs of employee training scores that
I need to convert to a more database friendly format.

Each tab has employee names, same ones for each tab, in the first
column. The top row contains the training course name, these may or
may not be different on each tab, so we can assume they are
different. The cells contain the employees scores for the course
name.

I need to write a macro that converts this format to one like
this......

employee name, course name, course score
employee name, course name, course score
employee name, course name, course score
employee name, course name, course score

More specifically......

Bob Jones, Excel 101, 4
Bob Jones, Excel 102, 5
Jan Smith, Word 101, 3
Jan Smith, Word 102, 5
Pat Boyd, Outlook, 4

I know how to code a macro to copy cell values to another tab but not
how to cycle through the rows and columns to create the format I
need. I have never really created a macro with loops, especially like
this. Hopefully all of this is clear.

TIA,

Paul

 

[Joel]

Sub createsummary()

Worksheets.Add before:=Sheets(1)
ActiveSheet.Name = "Summary"
SumRowCount = 1
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Summary" Then
ShRowCount = 2
CourseName = sh.Range("B1")
Do While sh.Range("A" & ShRowCount) <> ""
Person = sh.Range("A" & ShRowCount)
Score = sh.Range("B" & ShRowCount)
With Sheets("Summary")
.Range("A" & SumRowCount) = Person
.Range("B" & SumRowCount) = CourseName
.Range("C" & SumRowCount) = Score
SumRowCount = SumRowCount + 1
End With
ShRowCount = ShRowCount + 1
Loop
End If
Next sh

End Sub