Why Are My Macros Not Being Called?

andybason

Board Regular
Joined
Jan 7, 2012
Messages
173
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

Board Regular
Joined
Jan 7, 2012
Messages
173
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,646
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jan 7, 2012
Messages
173
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):

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

When you use "With Statements" like this:
VBA Code:
  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:
VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,424
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You aren't link the sheets with the With statement so it is acting on the ActiveSheet. For example

VBA Code:
   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
Rich (BB code):
   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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows
You are weclome.
Glad we could help!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,838
Messages
5,544,606
Members
410,626
Latest member
rkmadasu
Top