current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

My file has different sheets with the same-named sub routine inside each doing different things specific to the sheet. So, I'm trying to call dynamically a macro inside a selected sheet using Application.Run from a module. All sheets' "object" name (I don't know how to call those) are already modified to be the same as its view (inside the bracket) name, without the spaces. 

macro = Chr(39) & ThisWorkbook.Name & Chr(39) & "!" & Replace(SheetName, " ", "") & "." & "Harmoniser()"
Application.Run macro

Here's an example of the Harmoniser macro in a Sheet.

Sub Harmoniser()
    ActiveSheet.Range("k22").GoalSeek Goal:=0, ChangingCell:=Range("l13")
    MsgBox ("Done!")
End Sub

Somehow, only the MsgBox works, and it shows it twice everytime. Debugging by putting a break point doesn't work either. It happens to all of my sheets. Is there a limitation to Application.Run that it only allows certain code or there's an error that Excel is not giving me?

share|improve this question
    
Remove the () from the macro name and it will cure both problems. :) The code assumes the correct sheet is active - you probably want Me.Range("K22")... in the sheet code. –  Rory Jul 31 '14 at 14:41

1 Answer 1

up vote 2 down vote

I get the same thing when I run your code. I tried a few different tweaks and am able to enter debug/breakpoint mode if I do this:

Sub t()
Dim sht As Worksheet
Dim macro As String

For Each sht In ThisWorkbook.Worksheets
    sht.Activate
    macro = sht.CodeName & ".Harmoniser"
    Application.Run macro
Next
End Sub

Your code may have been erroring silently because you were using ActiveSheet without first Activating the sheet which contains the macro to be run. Not sure about the double-display of the MsgBox, but seems like that may be related.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.