I have only basic VBA knowledge and looking for help with VBA code to automate a process I do every week manually. The attached workbook is from Crystal Reports and has non-printing characters or leading/trailing spaces in the date column (Column A) which are not properly formatted as a short date. I currently use the Easy-XL add in to remove spaces before and after on all cells which solves that problem so the column can be properly formatted. However, I want this as part of the macro instead of having to do this step manually. The date from A2 must also be entered in the last row of Column A which would be A51 once the next step of inserting needed rows is accomplished. The report is always for one week and once processed will always have 51 rows of data (header+7 rows per day for 7 days+weekly total row=51)
In Column B, labeled "Acuity" there are missing rows. If no patients for that acuity came in Clarity does not put a zero in for null data. Hence the missing rows. However, where the data is exported to I must have a row matching each acuity for each date with zeroes for all null values or else I get errors. So in Column B I go down the column and see that for each date it goes 1,2, 3, 4, 5,6, and T and then on the very last row in this column is the weekly total row which comes in blank but must have a capital "W" inserted.
I have attached two versions of the file. The first one is how the data comes to me. The second one I processed how I would normally do. Any rows or data manually added are highlighted in yellow to make it easier for you to see what I am referring to. I do not normally highlight any cells.
I want the macro not only for my use but so I can pass it on to some other less Excel savvy users in my organization who want the same report but lack the knowledge to do the manual steps I perform. So a macro which I can assign a shortcut key will make it easier to pass on. My attempts at trying to write this macro on my own were far from successful.
Thank you for your assistance.