Calling a macro in another open workbook

[furstukin]

I have been building a series of macro spreadsheets that will keep track of our staffing numbers and save a new copy each week so we have a history of what has happened. The funny thing is the macros all work perfectly on my computer with out any issues at all, but my co-workers keep getting runtime errors about variables not being defined.

I went thru and defined all variables even though none of us have the require variable declaration option checked. But now I am still having some of my co-workers getting an error on this line of code

Range("C9996").Value = LTrim(Range("C9996").Value)

I had tried to declare that too but excel kept freezing up and crashing on me saying something about the variable not being validly defined or something.

The really really odd thing is the macro this piece of code is in happens to be inbedded in the actual comparison file as well as the master file that runs the whole show. If the macro is run out of the master file as part of a series of macros that is designed to do everything for you. My co-workers get the error. But if they run the macro by itself from the comparison file it works fine...???

So that's when I said ok fine I will just have my master file call the macro out of the comparison file but that is not working either.

I wrote this

DATE3 = Range("B1").Value
Application.Run "Miramar SSCOE Schedule Comparison " & DATE3 & ".xlsm!CHECKAGENTS"

But now it tells me the macro CHECKAGENTS can not be run from the Miramar SSCOE Schedule Comparison 060511.xlsm workbook because macros may be disabled. The comparison workbook is open and macros are not disabled so I am at a loss here.

The entire macro code that is screwing up is as follows.

Sub CHECKAGENTS()
'
' Looks at agents that are not on the old but are on the new and vice versa then adds them to the reference sheet.
' Macro recorded 8/9/2006 by ccccej
'
'
    Sheets("Old Schedule").Select
    Sheets("Old Schedule").Range("Q4:R10004").Select
    Selection.Copy
    Sheets("Reference Sheet").Select
    Range("c5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False
 
    Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 
    Range("A1").Select
    Range("C9996").Value = LTrim(Range("C9996").Value)
    Range("C9997").Value = LTrim(Range("C9997").Value)
    Range("C9998").Value = LTrim(Range("C9998").Value)
    Range("C9999").Value = LTrim(Range("C9999").Value)
    Range("C10000").Value = LTrim(Range("C10000").Value)
    Range("C10001").Value = LTrim(Range("C10001").Value)
    Range("C10002").Value = LTrim(Range("C10002").Value)
    Range("C10003").Value = LTrim(Range("C10003").Value)
    Range("C10004").Value = LTrim(Range("C10004").Value)
    Range("C10005").Value = LTrim(Range("C10005").Value)
    Range("c5:d10005").Select
    Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 
    Range("A1").Select
 
    Sheets("New Schedule").Select
    Sheets("New Schedule").Range("Q4:R10004").Select
    Selection.Copy
    Sheets("Reference Sheet").Select
    Range("f5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 
    Range("A1").Select
    Range("F9996").Value = LTrim(Range("F9996").Value)
    Range("F9997").Value = LTrim(Range("F9997").Value)
    Range("F9998").Value = LTrim(Range("F9998").Value)
    Range("F9999").Value = LTrim(Range("F9999").Value)
    Range("F10000").Value = LTrim(Range("F10000").Value)
    Range("F10001").Value = LTrim(Range("F10001").Value)
    Range("F10002").Value = LTrim(Range("F10002").Value)
    Range("F10003").Value = LTrim(Range("F10003").Value)
    Range("F10004").Value = LTrim(Range("F10004").Value)
    Range("F10005").Value = LTrim(Range("F10005").Value)
    Range("F5:G10005").Select
    Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 
    Range("A1").Select
End Sub

And as you can see from the date I di not write this it was done long before I started. And as long as it is run from the module with in the workbook it was designed for it works fine but if the macro is run from another workbook's module it causes it to error out for my co-workers

Any help here is appreciated asI am at a total loss as to why it errors out for my co-workers and why it is telling me it can't run the macro from the Schedule Comparison file.

 

[mikerickson]

The first thing that I notice is that all those ranges are unqualified. (i.e. they are of the form Range("A1") rather than Workbooks("Workbook1.xls").Sheets("Sheet1").Range("A1"))

That's a common cause for problems when working between workbooks.

Another problem is that Sheets("Old Schedule") is also unqualified and selecting might cause issues.

 

[furstukin]

The first thing that I notice is that all those ranges are unqualified. (i.e. they are of the form Range("A1") rather than Workbooks("Workbook1.xls").Sheets("Sheet1").Range("A1"))

That's a common cause for problems when working between workbooks.

Another problem is that Sheets("Old Schedule") is also unqualified and selecting might cause issues.

Well I am not too familiar with using qualified statements, but what I usually do is call up the workbook a given sheet is in before making a reference to it. Because the macro shown was the result of a script call it did not show the last line was

Workbook(COMPARE).Activate
Call CHECKAGENTS

So I figured Excel would just always try to pull from the active workbook unless I specified otherwise. As long as I always remember to add the code to activate the workbook I need before referencing it I should be fine right?

However, I will most definately try to make the ranges that are giving my co-workers errors qualified and see if it works for them. Thanks for the suggestion.