Link to home
Start Free TrialLog in
Avatar of kvrogers
kvrogers

asked on

Need Help with Excel Macro

I am attaching a MSWord document that lists the Macros I  have questions on.  I have inherited this workbook from a user who is no longer with the company.  I am not good AT ALL with macros.  I just wonder if you can explain to me what Macros PGcleanup1, PGcleanup2, PGcleanup3, PGcleanup4 in this spreadsheet do.  

Thanks
KR
H--PressGaneyCleanUp-Macro.docx
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

without seeing the spreadsheet they are working on its difficult to describe in English and as you have pasted them as pictures I can't show you as much detail as I'd like - because I'm not re-tpying them- see if this helps the:-

The macros all do pretty much the same three steps:
- each deletes (removes from the sheet) the first 39 rows of the sheet its run from with the first lines between Rows....:and.... Xlup. NB the first one empties the cells before deleting them but that's probably irrelevant
- it then moves (cut/paste) a block from the remaining data from Colum a to column I - each one removes a slightly different black (Range("A1:E10") for example). that covers the lines between Range(....and.... Paste).
- the final piece deletes an area that's slightly larger than the block it just moved, so actually only affect columns F and G (because columns a-e have already been 'cut' in step 2. these lines are between  Range("a1:G....and.... Xlup)..

From the look of it these were created with 'record macro' rather then 'written' in VBA so I would guess they are an attempt to automate the removal and re-ordering of some data that needs to be done on a regular basis. applying them to the same sheet more than once would delete all the rows in a sheet fairly quickly unless there is another process adding information - such as importing a report or data from another application.
Avatar of kvrogers
kvrogers

ASKER

I am attaching spreadsheet.  This is not patient information as it is a survey report.  Names listed are physician names not patient names.
H--PressGaney-_2015-Q1_Kate_102115.xlsm
OK, each tab (doctor's surname?) has a set of data that is being summarised on the 'summary' tab and then analysed on the Pivot table. I would guess that the tabs are copied into the sheet on a regular, probably quarterly, basis.

Assuming the tabs you sent have not already been through the process the 'cleanup' macros are clearing information out down to the section starting 'provider' then copying that section to a block at the top right (column I). I would guess that each different cleanup applies to different providers who must fill the details in slightly differently, but I've no way of figuring out which one applies to which sheet.

The main macro PressGaney then takes the information from the sheets (presumably after they have been 'cleaned' and fills in the Summary sheet. Once that's done it adjusts the names.

My guess is that you would then go an refresh the pivot table to get the analysis that's needed.

To be brutally honest if whoever created these is not available to explain the thinking - or didn't leave a set of notes about how to do the process - you are probably better off describing what's needed and getting a proper piece of code written, people on here will help. This is a good example of someone automating their own work but its very flaky and prone to a lot of mistakes and errors if you aren't the one who invented it.

Alternatively, if you want to start learning how macros work:- instead of pressing 'run' when you execute a macro press 'step into', and you will see each command as it processes (press f8 to move to the next one) you can then observe its effect on the sheet and this will help you work it out. Again if something doesn't seem to make sense you can get help here.
ASKER CERTIFIED SOLUTION
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for all your help.  My thoughts exactly!.  I hate trying to step in, with no guidance, and clean up someone elses mess.   I have emailed the requestor for the report and asked them EXACTLY what they are trying to accomplish by these macros.

I updated the SUMMARY macro, as I figured that one out, with about 10 new names that they wanted and them put the SUMMARY sheet in Alpha order as they requested.  I even found where some of the original code was incorrect.  

My thinking is that with adding these additional providers and putting sheet in Alpha order this is now changing the ENTIRE cleanup process.  

KR
I know the feeling of trying to work out someone else's thoughts and the problem you will face is that even if you get something that appears to work you will have to spend a ferocious amount of time proving it to yourself and always knowing that one day a file may come in a slightly different format that you hadn't anticipated - and then you'll spend a similar amount of time fixing it whilst all about you cast aspersions.

Definitely a case of no up side whereas right now you can point to the person who left and get approval to do it properly.
Very helpful information.  Thank you very much.

KR
I ran the macro on first doctor and now have #REF error on Summary page.  They will be calling me.

KR
I'm afraid its the first of many :(

 #ref  indicates that you have a formula pointing to a cell that's been deleted, can you backtrack and figure out which cell that is?