Complicated

[HigherEd]

I'm in the midst of building a macro to create an itemized invoice based on the prices of a series of course.

Sheet 1
In column A I have the Course #, column B the Course Name, column C part time or full time ("PT" or "FT"), and in column D the Course Price.

Some courses only appear once. Some courses appear multiple times. Not each course is the same price (e.g. FT courses could be worth $1404 or $1038, PT courses could be worth $702 or $336).

Sheet 2 ("Billing Statement")
Starting in row 18, in column B I need to concatenate column A, B and C (with parentheses for PT or FT) from sheet one.

For example:

"BSN1001 - Intro (FT)"

In column F I need the totals for each course for both part time and full time.

And column E needs to automatically populate the letter "T" if text exists in a row in column B.

Again, the number of courses I need to itemize can vary from month to month, so I need something that can adapt to all of these variables.

I am open to ANY suggestions or advice. My goal is to have this work done by the click of one button.

 

[Veritan]

Try this code and see if it does what you need.

Option Explicit
Sub Billing()
    Dim Courses As Range, r As Range, LastClassRow As Long, arrCourses() As Variant
    Dim i As Long, Classes As Long
    
    Application.ScreenUpdating = False
    Sheets("Sheet1").Activate
    Set Courses = Sheets("Sheet1").Range("A2", Range("A2").End(xlDown))
    LastClassRow = Range("A2").End(xlDown).Row
    Classes = LastClassRow - 1
    i = 1
    ReDim arrCourses(1 To Classes)
    For Each r In Courses
        arrCourses(i) = r & " - " & r.Offset(, 1) & " (" & r.Offset(, 2) & ")"
        i = i + 1
    Next r
    i = 1
    Range("A2", Range("A2").End(xlDown)).Copy
    Sheets("Billing Statement").Activate
    Range("G18").PasteSpecial
    Range("G18").End(xlDown).RemoveDuplicates Columns:=1, Header:=xlNo
    Range("F18", Range("G18").End(xlDown).Offset(, -1)).FormulaR1C1 = _
        "=SUMIF('Sheet1'!R2C1:R" & LastClassRow & "C1,'Billing Statement'!RC[1],'Sheet1'!R2C4:R" & LastClassRow & "C4)"
    For Each r In Range("F18", Range("F18").End(xlDown))
        r = r.Offset(, 1) & " Total: " & Format(r, "$#,##0")
        r.Offset(, 1).ClearContents
    Next r
    For Each r In Range("B18", Range("B" & Classes + 17))
        r = arrCourses(i)
        i = i + 1
    Next r
    For Each r In Range("E18", Range("B18").End(xlDown).Offset(, 3))
        If r.Offset(, -3) <> "" Then r = "T"
    Next r
    Columns.AutoFit
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

I'm assuming that your 2nd sheet's tab name is Billing Statement and that your first sheet's tab name is just Sheet1. Also note that the code assumes there is nothing in column G. If there is, this will overwrite it. Insert the above code into a module and then run it. You may need to modify it slightly based on how you want the information to appear. Other than that, it should work, hopefully.