Macro to unmerge and copy down the cell information

[bowecas2]

I have been asked to develop some tracking sheets for individual performance across my work group. I've been provided a report that summarizes how many tasks an individual performed across several steps in our processes. Unfortunately, I don't have any control on how the report coming to me is made. The report is made weekly, I need to pull the data each week for charts showing how each person is doing. My current solution is using sumifs statements to sum each of the categories on separate sheets. The problem is, the first column with the different steps in our process uses merged cells, which causes sumifs statements to ignore the additional lines under each section. Because our work load varies, there isn't a set number of rows for each step, so one week step 1 may have 4 employees working on it, and the next week it may have 7. How would I write a macro to unmerge the cells in Column A, then copy down the name of each step so that a sumifs formula would be able to pull the data for each employee? Feel free to let me know any additional information that you might need.

[Greg M]

Hi there,

See if the attached workbook gets you moving in the right direction - it uses the following formula in a new column B which is being used as a helper column and may be hidden if required:

Please Login or Register  to view this content.

The above formula is inserted in cell B2 and is copied down as required.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[harrisonland]

If you do prefer to go down the VBA route, you could try this:

Please Login or Register  to view this content.

Might be convenient if you're automating other stuff in the workbook, or if there's a large number of "Steps"

Tim

[bowecas2]

That code is working perfectly! Thanks!!! My only other question is how would I set it to run only on a specific sheet? I am trying to set up a startup sheet where I have buttons to run the various macros to update the spreadsheet. Right now when I connect it to a button on the startpage, it puts the information on that sheet instead of putting it on the data sheet.

[harrisonland]

Just change the bit that says "Sheet1.UsedRange.Find... " to point to whichever sheet you need it to run on, e.g.

Please Login or Register  to view this content.

Tim