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
kvrogersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
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.
0
kvrogersAuthor Commented:
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
0
regmigrantCommented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

regmigrantCommented:
here some commentary to start you off:

    Rows("1:39").Select                             'Select the first 39 rows 
    Application.CutCopyMode = False   'ignore this, excel housekeeping
    Selection.ClearContents                    'clear the contents (blank all the cells)
    Selection.Delete Shift:=xlUp             'delete the rows and move the rows below 'up' (to start at row 1)
    Range("A1:D11").Select                     'select the block now at a1 to d11 (the 'Provider' section)
    Selection.Cut                                      'cut them from the worksheet
    Range("I1").Select                              'select column I row 1
    ActiveSheet.Paste                             ' paste the provider data we cut previously
    Range("A1:G11").Select                  ' Select an area slightly large than the provider information
    Selection.Delete Shift:=xlUp         ' delete the data (the copy stays in column I)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvrogersAuthor Commented:
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
0
regmigrantCommented:
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.
0
kvrogersAuthor Commented:
Very helpful information.  Thank you very much.

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

KR
0
regmigrantCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.