Call procedures from a procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have a procedure that calls several other procedures, e.

Sub AllFIles(

CopyData
CopyData
CopyData

End Su

Theres nothing special about the CopyData procedures (they open a file, filter and copy data to a central file).
The problem I'm having is that when I run the AllFiles macros, it runs the CopyDataA procedure but then rather than control reverting back to Sub AllFiles() and running CopyDataB, it simply ends with CopyDataA
Does anyone know why? am I doing something wrong

Many thank

Jimb
 
Post the code for CopyDataA

Jim said:
Hello,

I have a procedure that calls several other procedures, e.g

Sub AllFIles()

CopyDataA
CopyDataB
CopyDataC

End Sub

Theres nothing special about the CopyData procedures (they open a file,
Click to expand...
filter and copy data to a central file).
The problem I'm having is that when I run the AllFiles macros, it runs the
Click to expand...
CopyDataA procedure but then rather than control reverting back to Sub
AllFiles() and running CopyDataB, it simply ends with CopyDataA.
 
My first guess is you have an "End" statement in CopyDataA--Not "End If" or "end
sub", just "End". This says to stop the execution of your macro.

If that isn't it, you may want to step through your code and see where it's
stopping.

Post back with more info (snippet of code, not workbook) if that didn't help.
 
without seeing your code...

ensure that there is no "END" statement in your procedures. Step through
AllFiles using the F8 key , when CopyDataA is highlighted Shift-F8 will run
the proc and the CopyDataB should be highlighted if you first call returns
as expected

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
Jim said:
Hello,

I have a procedure that calls several other procedures, e.g

Sub AllFIles()

CopyDataA
CopyDataB
CopyDataC

End Sub

Theres nothing special about the CopyData procedures (they open a file,
Click to expand...
filter and copy data to a central file).
The problem I'm having is that when I run the AllFiles macros, it runs the
Click to expand...
CopyDataA procedure but then rather than control reverting back to Sub
AllFiles() and running CopyDataB, it simply ends with CopyDataA.
 
Not without seeing the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Jim said:
Hello,

I have a procedure that calls several other procedures, e.g

Sub AllFIles()

CopyDataA
CopyDataB
CopyDataC

End Sub

Theres nothing special about the CopyData procedures (they open a file,
Click to expand...
filter and copy data to a central file).
The problem I'm having is that when I run the AllFiles macros, it runs the
Click to expand...
CopyDataA procedure but then rather than control reverting back to Sub
AllFiles() and running CopyDataB, it simply ends with CopyDataA.
 
Heres the code...Ok its not the most sophisticated.

Sub UpdateAll(

UpdateSalarie
UpdateAgenc
UpdateCarAllowanc
UpdatePubli
UpdateSubsistenc
UpdateTrainin
UpdateConsultanc
UpdateCareban

End Su
-----------------------------------------------------------------------------------------------------
Sub UpdateSalaries(

Dim MyMonth As Lon
MyMonth = Range("AB7"
Application.ScreenUpdating = Fals
Sheets("****").Selec

If MyMonth <= Range("AC29") The
ExitForm.Sho
Els
Counter =
For Counter = 1 To 100000000
Set Curcell = Worksheets("*****").Cells(Counter, 1
If Curcell.Value = "" The
Range("A1").Offset(Counter - 1, 1).Selec
Workbooks.Open FileName:=
"c:\*******.xls" 'name withel
Range("A1").Selec
With Range("A1").CurrentRegio
.AutoFilte
.AutoFilter Field:=1, Criteria1:=MyMont
.SpecialCells(xlCellTypeVisible).Cop
Windows("*****.xls").Activat
Range("A1").Offset(Counter - 1, 0).PasteSpecial xlPasteAl
Windows("*********.xls").Activat
.AutoFilte
Application.CutCopyMode = Fals
Application.DisplayAlerts = Fals
ActiveWindow.Clos
Application.DisplayAlerts = Tru
IntEnd = MsgBox("Data retrived", vbInformation + vbOKOnly
End Wit

En
Els
End I
Next Counte
End I
Application.ScreenUpdating = Tru
End Su

and then there are the 7 other procedures which are identical to the one above but the files are different.

Does that help

Jimbo
 
Jim,

The problem is that you have an "End" statement, immediately
following the End With. End by itself causes all VBA execution to
cease; it does not end just the current procedure. Most likely,
you will want to change this to 'Exit Sub' which will cause code
execution to leave that procedure and return to the calling
procedure.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


Jim said:
Heres the code...Ok its not the most sophisticated..


Sub UpdateAll()

UpdateSalaries
UpdateAgency
UpdateCarAllowance
UpdatePublic
UpdateSubsistence
UpdateTraining
UpdateConsultancy
UpdateCarebank

End Sub
--------------------------------------------------------------- ---------------------------------------
Sub UpdateSalaries()

Dim MyMonth As Long
MyMonth = Range("AB7")
Application.ScreenUpdating = False
Sheets("****").Select

If MyMonth <= Range("AC29") Then
ExitForm.Show
Else
Counter = 1
For Counter = 1 To 1000000000
Set Curcell =
Click to expand...
Worksheets("*****").Cells(Counter, 1)
If Curcell.Value = "" Then
Range("A1").Offset(Counter - 1, 1).Select
Workbooks.Open FileName:= _
"c:\*******.xls" 'name witheld
Range("A1").Select
With Range("A1").CurrentRegion
.AutoFilter
.AutoFilter Field:=1, Criteria1:=MyMonth
..SpecialCells(xlCellTypeVisible).Copy
Windows("*****.xls").Activate
Range("A1").Offset(Counter - 1, 0).PasteSpecial xlPasteAll
Windows("*********.xls").Activate
.AutoFilter
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
IntEnd = MsgBox("Data
Click to expand...
retrived", vbInformation + vbOKOnly)
End With

End
Else
End If
Next Counter
End If
Application.ScreenUpdating = True
End Sub

and then there are the 7 other procedures which are identical
Click to expand...
to the one above but the files are different..
 
Hi Jim
the problem is within the lines

End With
End
Else

The single 'End' statement stops the VBA execution. You may either
delete this statement or make this an 'Exit sub' statement
 
Did you see where everyone has surmised you had an END statement, and then
after posting your code, pointed out you have an END statement?
 
I didn't know that an End statment in the middle of the code stopped it then and there, I was focusing on the last line of the procedure the End Sub

Thanks to everyone for their help, its most appreciated. Would have taken me hours to figure that out

Best Regard

Jimbo
 
Back
Top