cycling through a raw data report and creating multiple new reports

*VBA noob*
*sample workbook attached*

i receive a report every quarter that is similar to the "raw data" sheet, where the values in column A are departments and the values in column B are customers. there are 50+ departments and each department has a varying number of customers each quarter, some customers are in multiple departments.

I would like to create a program that grabs data from "raw data" for each department, popluates the "report" sheet with that data, saves the "report" sheet as a PDF using the department name in the filename, clears the report after save and moves down to the next department in "raw data" to repeat the process.

I am having trouble determining how best to accomplish this goal; i can imagine calling multiple subroutines would perform this cleanly but I'm not comfortable with my ability to create them.

the idea I have is to begin the PROCESS
at "a3" in "Raw Data", do nothing until <>" ", name the notblank cell "department" and establish a range based on "department" to encapsulate the associated data: Range(.Offset(0, 1), .Offset(0, 4).End(xlUp).Offset(1, 0)).Name = "departcustomers"
next, the "report" sheet would populate itself using IndexMatch formulas {formulas a, b, c} in c5:e15, formula a in column C, b in column D and c in cloumn E.
then, the "report" sheet would copy a1:a2 from "raw Data" into "report" c1:c2 and copy "department" from "raw data" into "report"c3.
at this point, "report" should look like "populated report 2036" and "report" should be saved as a pdf to the desktop {macro a}.
finally, I want to clear "report" c:e 'i'm not sure if this is necessary
repeat the PROCESS until there are no more departments, starting from the row below "department"

I think this captures the intent of what I would like ...


I am unsure if the most effective route would be to set up a main sub that calls the other subs like:

Please Login or Register  to view this content.

Thanks in advance for the advice and assistance
-rufus




{formula a}
=if(isna(index([range"departcustomers".column 2],match("raw data" $b5, [range"departcustomers".column 1],0))),0,index([range"departcustomers".column 2],match("raw data" $b5, [range"departcustomers".column 1],0))

{formula b}
=if(isna(index([range"departcustomers".column 3],match("raw data" $b5, [range"departcustomers".column 1],0))),0,index([range"departcustomers".column 3],match("raw data" $b5, [range"departcustomers".column 1],0))

{formula c}
=if(isna(index([range"departcustomers".column 4],match("raw data" $b5, [range"departcustomers".column 1],0))),0,index([range"departcustomers".column 4],match("raw data" $b5, [range"departcustomers".column 1],0))

{macro a}

Please Login or Register  to view this content.

Hi rufuswilson
I'm certain there are a half dozen or so lines of code that can do as you require...well you're getting the "Blue Light" special...many lines that appear to do as you require.
You'll need to change this line of code to tell the procedure where to save the .pdf files (don't forget the trailing backslash (\)).

Please Login or Register  to view this content.

See attached. Run the code from the button in Report worksheet...let me know of issues.

Hi rufuswilson

What does this line of code look like in your file?

Please Login or Register  to view this content.

Hi rufuswilson

Is there a folder on your desktop called myPDFs?

You're welcome...glad I could help.