austenr
09-04-2007, 08:49 AM
I need to pull data from the sheet called "summary" to a sheet called "report" in the attached workbook. The code below is what builds the report.
Sub BuildReport()
Dim sht As Object
Dim intNum As Integer
Sheets("Report").Activate
intNum = 0
ActiveSheet.AutoFilterMode = False
Range("baseTable").CurrentRegion.Clear
Range("baseTable").Value = "Case"
Range("baseTable").Offset(0, 1).Value = "Student"
Range("baseTable").Offset(0, 2).Value = "Cold Call"
Range("baseTable").Offset(0, 3).Value = "Score"
Range("baseTable").Offset(0, 4).Value = "Comment"
Range("baseTable").Offset(0, 5).Value = "Scribe's Comment"
Range("baseTable").Offset(0, 6).Value = "M/F"
Range("baseTable").Offset(0, 7).Value = "Foreign"
Range("baseTable").Offset(0, 8).Value = "US"
Range("baseTable").Offset(0, 9).Value = "Citizenship"
Range("baseTable").Offset(0, 10).Value = "Area"
For Each sht In ActiveWorkbook.Sheets
Select Case sht.Name
Case "Instructions"
Case "Summary"
Case "Report"
Case "base case"
Case Else
' paste case name, first col
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 0).Resize(120, 1).Value = sht.Name
' paste student name, 1 cell over
Sheets("Summary").Range("insertHere").Offset(8, 0).Resize(120, 1).Copy
Sheets("Report").Activate
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 1).Select
Selection.PasteSpecial (xlPasteValues)
' paste cold call column, 2 cells over
sht.Range("B4:B123").Copy
Sheets("Report").Activate
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 2).Select
ActiveSheet.Paste
' paste eval & comments cols, 3 cells over
sht.Range("E4:G123").Copy
Sheets("Report").Activate
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 3).Select
ActiveSheet.Paste
' paste m/f, seating area
Sheets("Summary").Range("insertHere").Offset(8, 6).Resize(120, 2).Copy
Sheets("Report").Activate
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 6).Select
Selection.PasteSpecial (xlPasteValues)
intNum = intNum + 1
End Select
Next sht
Sheets("Report").Range("baseTable").Select
Selection.AutoFilter
DeleteEmptyRows
'add the formulas that were messed up when the rows were deleted
Range("Sum").Formula = "=SUBTOTAL(9,E10:E15000)"
Range("Average").Formula = "=SUBTOTAL(1,E10:E15000)"
Range("Count").Formula = "=SUBTOTAL(2,E10:E15000)"
Range("StdDev").Formula = "=SUBTOTAL(7,E10:E15000)"
End Sub
I had to add several fields to the end of the report "Foreign", "US", "Citizenship" and "Area".
I know the Case Else is where the data is being pulled from the summary sheet but it looks like several fields are being multiplied by 120. Im not sure how to move the new data over because I dont understand what the Case statements are doing. Can someone explain them? Thanks
Sub BuildReport()
Dim sht As Object
Dim intNum As Integer
Sheets("Report").Activate
intNum = 0
ActiveSheet.AutoFilterMode = False
Range("baseTable").CurrentRegion.Clear
Range("baseTable").Value = "Case"
Range("baseTable").Offset(0, 1).Value = "Student"
Range("baseTable").Offset(0, 2).Value = "Cold Call"
Range("baseTable").Offset(0, 3).Value = "Score"
Range("baseTable").Offset(0, 4).Value = "Comment"
Range("baseTable").Offset(0, 5).Value = "Scribe's Comment"
Range("baseTable").Offset(0, 6).Value = "M/F"
Range("baseTable").Offset(0, 7).Value = "Foreign"
Range("baseTable").Offset(0, 8).Value = "US"
Range("baseTable").Offset(0, 9).Value = "Citizenship"
Range("baseTable").Offset(0, 10).Value = "Area"
For Each sht In ActiveWorkbook.Sheets
Select Case sht.Name
Case "Instructions"
Case "Summary"
Case "Report"
Case "base case"
Case Else
' paste case name, first col
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 0).Resize(120, 1).Value = sht.Name
' paste student name, 1 cell over
Sheets("Summary").Range("insertHere").Offset(8, 0).Resize(120, 1).Copy
Sheets("Report").Activate
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 1).Select
Selection.PasteSpecial (xlPasteValues)
' paste cold call column, 2 cells over
sht.Range("B4:B123").Copy
Sheets("Report").Activate
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 2).Select
ActiveSheet.Paste
' paste eval & comments cols, 3 cells over
sht.Range("E4:G123").Copy
Sheets("Report").Activate
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 3).Select
ActiveSheet.Paste
' paste m/f, seating area
Sheets("Summary").Range("insertHere").Offset(8, 6).Resize(120, 2).Copy
Sheets("Report").Activate
Sheets("Report").Range("baseTable").Offset(1 + (120 * intNum), 6).Select
Selection.PasteSpecial (xlPasteValues)
intNum = intNum + 1
End Select
Next sht
Sheets("Report").Range("baseTable").Select
Selection.AutoFilter
DeleteEmptyRows
'add the formulas that were messed up when the rows were deleted
Range("Sum").Formula = "=SUBTOTAL(9,E10:E15000)"
Range("Average").Formula = "=SUBTOTAL(1,E10:E15000)"
Range("Count").Formula = "=SUBTOTAL(2,E10:E15000)"
Range("StdDev").Formula = "=SUBTOTAL(7,E10:E15000)"
End Sub
I had to add several fields to the end of the report "Foreign", "US", "Citizenship" and "Area".
I know the Case Else is where the data is being pulled from the summary sheet but it looks like several fields are being multiplied by 120. Im not sure how to move the new data over because I dont understand what the Case statements are doing. Can someone explain them? Thanks