Call sub in vba excel not working properly

arnau infante pinós

I need to call a macro from another macro and I'm using the 'call' way. I don't understand why it doesn't work properly, unless you put a breakpoint in the call line.

See. I have this:

Sub actualizarDatos()   
     Sheets("DATOS CITAS").Select
     Range("A1").Select
     Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

     ActiveWorkbook.RefreshAll

     Call replaceColumns

     Sheets("Resumen").Activate
End Sub

The first part is written like this because I had some problems with a large sheet. It made my excel stop.

The macro that I'm calling looks like this:

 Sub replaceColumns()    
   With ThisWorkbook.Sheets("RFS")
       .Columns("A").Replace _
          What:="2", Replacement:="2", _
          SearchOrder:=xlByColumns, MatchCase:=True
   End With
End Sub

Why do I need the second macro? Because a column with formula didn't update automatically, so this macro "modify" a cell which is been used in the formula.

But let's go with the important part and the main qüestion:

Why the call part is not working? -if I execute the first macro in RFS sheet(The sheet in the second macro) it works! But I have to update it from a button in another sheet, so it isn't the answer. - if I put a breakpoint and execute the first macro from in a random sheet, it works.

I've also tried with the macro name directly, not using 'Call'. Not working. And even writing the macro content directly inside the first macro, where the call line is written, it doesn't work!! For some reason it misses that line. Does anyone know why?

Thank you!

[EDIT]

SOLVED

I had to do something in the configuration of my tables. If someone have the same problem with the refreshing, just to the next:

For each table you will refresh in RefreshAll, select it and go to Design in the top menu. Now go to properties and uncheck the checkbox that says: Enable backgroud refresh. This is how it looks:

properties window to change the refresh settings of a table

Alex Martinez

Did you try making the Sub Public. Like:

Public Sub replaceColumns()    
   With ThisWorkbook.Sheets("RFS")
       .Columns("A").Replace _
          What:="2", Replacement:="2", _
          SearchOrder:=xlByColumns, MatchCase:=True
   End With
End Sub

Also, for the first procedure, try to put a Sleep command to ensure you wait until Excel finishes refreshing.

Sub actualizarDatos()   
     Sheets("DATOS CITAS").Select
     Range("A1").Select
     Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

     ActiveWorkbook.RefreshAll
     Sleep 2000
     Call replaceColumns

     Sheets("Resumen").Activate
End Sub

And put this code in the top of module:

#If VBA7 Then
  Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
   Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Excel VBA - Weekday and/or datediff function not working properly

From Dev

Excel VBA Custom sort not working properly

From Dev

Excel VBA code Private Sub Worksheet Change Power function not working

From Dev

Date separators not working properly in 64 bit Excel VBA

From Dev

Sub or Function in VBA Excel

From Dev

Sub-domain is not working properly

From Dev

Error while Call Sub in VBA

From Dev

Excel VBA - Skip sub or end sub IF Statement

From Dev

Excel VBA Sub + Private Sub Error

From Dev

Insert VBA sub into Excel Worksheet

From Dev

"Argument Not Optional" VBA Excel SUB

From Dev

Excel: Setup of validation in a VBA sub

From Dev

EXCEL VBA - Define Sub or Function

From Dev

Excel VBA object sub call with 2 object parameters gives compile error: expected =

From Dev

Match function? VBA not working properly

From Dev

Count if function not working properly in vba

From Dev

Excel vba pastespecial not working

From Dev

excel VBA code not working

From Dev

Excel VBA If not equal not working

From Dev

Update is not working Excel VBA

From Dev

Microsoft Excel formula not working properly

From Dev

excel: conditional formatting not working properly

From Dev

Call a VBA sub using a string value

From Dev

Call Another Sub in The Same Sheet VBA

From Dev

VBA call a SUB in a Sheet from module

From Dev

VBA to Call Sub If Text in Range is Found

From Dev

Call sub not working but code works when placed in sub that is doing the calling

From Dev

_Click() event not firing sub VBA Excel

From Dev

Excel VBA: Compile Error: Sub of Function not Defined