VBA Script to Combine Worksheets

[Mizzou.2018]

Hello,

I have a workbook that will show provider information and the provider monthly ratings. Each month information is copied and pasted into this workbook. The new worksheet is given the name of the month and year in the MMM YYYY format. The information copied and pasted should be in the same column order and should always have the same columns, but that is not guaranteed.

I want a script that will automatically combine each worksheet titled MMM YYYY (example: Apr 2019) into the "All Data" worksheet. Worksheets titled something other than MMM YYYY should not be combined. I would prefer the order of columns be the same as in the MMM YYYY worksheets. If there is a column that is added to one worksheet but is absent on the others, the script should include it in the "All Data" as well. I have no preference as to where it is added as I'm not sure this will happen, but want to be prepared in case it does. The "All Data" worksheet will include duplicate providers, but each row will be for different months rating.

Once I have this completed, I will want the script to list the providers and their averaged rating on the 4 average worksheets (All Average, Last Quarter Average, Last 6 Month Average, and Last 12 Month Average). On these average worksheets the provider will only be listed once and include an average of the scores from the "All Data" Tab. I am hoping there is a VBA script that can read the "provnum" (in case the provider name changes) and determine the average depending on the worksheet its in. The script would need to read the last column which shows the date "FILEDATE". The "All Average" worksheet will be the average for that column on all data the entire workbook for that specific provider. The "Last Quarter Average" worksheet will be the average for that column on all data for the last completed quarter (Q1: Jan-Mar, Q2: Apr-Jun, Q3: July-Sept, Q4: Oct-Dec). The "Last 6 Month Average" will be the same concept, but for the last rolling 6 months. The "Last 12 Month Average" will be the same concept, but for the last rolling 12 months.

In the 4 average worksheets the only information that should be included is the Provider ID (PROVNUM), Provider Name (PROVNAME), Overall Rating (OVERALL_RATING), Survey Rating (SURVEY_RATING), Quality Rating (QUALITY_RATING), Staffing Rating (STAFFING_RATING), and RN Staffing Rating (RN_STAFFING_RATING).

I also may add a worksheet that will reflect each provider visually over a period of time by a line graph. Any suggestions on this would be appreciated.

[Keebellah]

Hi, what you're asking is for someone to write an entire application for you, not HOW TO.
If you search for what's possible, I'm sure you can find things that will point you in the right direction.
Most of us here are here to help, designing and creating applications may be what some of us do for a living, but this forum is really here to help people struggling with working samples.
Think about that, Google is still a person's best friend when it comes to looking for solutions and ideas and of course we can help when you need to tweak or edit.

[xladept]

Hi Mizzou,

Try this:

Please Login or Register  to view this content.

*Allows for extra fields