Why Are My Macros Not Being Called?

[andybason]

Hello

I have this macro to call other macros. It calls all of them apart from the last two (which are ordinary subs and work when I run them manually).

Can anyone see what the problem is?

Thank you

Sub RunMacros()
    Application.ScreenUpdating = False
Call RunGrMacros
Workbooks("GrAnalytics.xlsm").Close SaveChanges:=False
Call RunBaMacros
Call CopyWw
Workbooks("BaAnalytics.xlsm").Close SaveChanges:=False
Call RunBoMacros
Call CopyWwBo
Workbooks("BoAnalytics.xlsm").Close SaveChanges:=False
Call SummariseBa
Call SummariseBo
Call SummariseGr
Call CopyWwGr
Call HighlightBa
Call HighlightBo
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

I would recommend taking a look at the last one that does run, and checking out the code, especially near the end.
Is there anything that closes Excel, or the workbook containing all the macros?

Note that there are various debugging techniques you can use to try to figure out what is going on.
For example, you can place a MsgBox at the top of each macro that does something like this:

MsgBox "RunGrMacros" running

Then you would see which macros are running.

Then, you could place a break point in the last one, run the Macros again up to that point, then step through your code line-by-line from that point forward (using the F8 key) to watch what is happening.

 

[andybason]

Thank you Joe. It's strange. There's nothing in the earlier macros that closes the workbook required for the macros. I tried placing Stop at the beginning of each then went through line by line using F8 and everything worked ok.

 

[RoryA]

If you hit the Stop lines, then the macros are being called, they're just apparently not doing what you expect. Without knowing what any of your code actually does, it's pretty hard to comment specifically, but my guess would be that either the wrong sheet is left active and your code isn't properly qualified, or it's a timing issue - eg you're refreshing queries but the subsequent code is running before those queries have actually finished executing.

 

[Joe4]

So everything works OK when you run it step-by-step with the F8 key, but not when you run it all at once?

Try putting a MsgBox at the top of each Macro that returns the name of the Macro, then run it all at once, and identify the macro in the last MsgBox you get.
Then post the VBA code for that Macro so we can see what it is doing.

 

[andybason]

Thank you everyone. I think you've helped me pinpoint the issue.

This is the first macro that doesn't run (the other is similar):

Sub HighlightBa()
Dim r As Long, c As Long
Dim sngCell As Single

   With ThisWorkbook.Sheets("Summary")
  
    For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) <> "" And Cells(r, c) >= Cells(9, c) * 1.05 And Cells(r, c) < Cells(9, c) * 1.1 Then Cells(r, c).Interior.Color = RGB(211, 222, 241)
      Next c
   Next r
  
   For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) <> "" And Cells(r, c) >= Cells(9, c) * 1.1 And Cells(r, c) < Cells(9, c) * 1.15 Then Cells(r, c).Interior.Color = RGB(180, 198, 231)
      Next c
   Next r
  
    For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) <> "" And Cells(r, c) >= Cells(9, c) * 1.15 Then Cells(r, c).Interior.Color = RGB(110, 145, 208)
      Next c
   Next r
  
    End With
End Sub

The macro called before this results in a different sheet being activated. Whenever I've tested HighlightBa it has been with the applicable sheet activated. When I tested it just now with a different sheet activated it did not work.

I thought the ThisWorkbook line would get round this but it seems not (please go easy I'm a VBA novice!)

Can anyone suggest an amendment?

Thank you

 

[Joe4]

When you use "With Statements" like this:

  With ThisWorkbook.Sheets("Summary")

when you refer to ranges within that "With" block, if you want them to apply to that sheet, you need to put a period in front of your range references, like this:

If .Cells(9, c) <> "" And .Cells(r, c) <> "" And .Cells(r, c) >= .Cells(9, c) * 1.05 And .Cells(r, c) < .Cells(9, c) * 1.1 Then .Cells(r, c).Interior.Color = RGB(211, 222, 241)

Otherwise, if there is no sheet reference, then it will simply default to the sheet that is the active sheet when it hits this part of code (and then your WITH block line is worthless).
So, I think you need to update all your range references with periods within you "With" block.

 

[MARK858]

You aren't link the sheets with the With statement so it is acting on the ActiveSheet. For example

   With ThisWorkbook.Sheets("Summary")

    For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) <> "" And Cells(r, c) >= Cells(9, c) * 1.05 And Cells(r, c) < Cells(9, c) * 1.1 Then Cells(r, c).Interior.Color = RGB(211, 222, 241)
      Next c
   Next r

should be

   With ThisWorkbook.Sheets("Summary")

    For r = 10 To 29
      For c = 4 To 63
         If .Cells(9, c) <> "" And .Cells(r, c) <> "" And .Cells(r, c) >= .Cells(9, c) * 1.05 And .Cells(r, c) < .Cells(9, c) * 1.1 Then .Cells(r, c).Interior.Color = RGB(211, 222, 241)
      Next c
   Next r

Notice the fullstops in front of each Cells.

 

[andybason]

Thank you guys, much appreciated.

 

[Joe4]

You are weclome.
Glad we could help!