Macro only executes in Personal Macro Book

[AlbinoRyno88]

Hello,

I have a macro I found on another forum that I really like to use. The macro will search every cell in the active sheet to see if they are merged. If they are, it stores the range of the merged cells in a temp. range variable, unmerges the cells, then fills the range with the value of the first cell in the unmerged range (what the value was).

The macro is saved in my personal macro workbook. I've tried lots of different ways and I cant seem to make the macro excuted on any spreadsheet I am unless i copy and paste the data into the personal macro workbook then excuted the macro. Can anyone help me figure out why it would only run in my personal macro book but not any other workbook even though the code says "active workbook?"

Thanks in advaced for your help. I have placed the code below.

Please Login or Register  to view this content.

[mehmetcik]

Try this

Please Login or Register  to view this content.

[brant.wheeler]

To help you understand a little bit better what mehmetcik did here...

When you are running code, ThisWorkbook will always refer to the workbook where the code is located. So instead of looking in the current ActiveSheet in Excel, it is looking at the ActiveSheet in the workbook where the code is located. By removing ThisWorksheet from your code, the macro will run for the current ActiveSheet in all of Excel instead of only looking within the workbook that contains the code.

[AlbinoRyno88]

Awesome! Yes it worked exactly how I wanted it! Thank you mehmetcik for fixing my code! And thank you brant.wheeler for explaining what was changed. I'm still new to the vba coding and I usually just record my macros but doing that limits me. Slowly but surely I will get better. Thanks again to both of you for your help!