Running A Macro From Within Another Macro

sub cut_past_col
If StrComp(Range("AA4"), "Prelim Dwg sent to AB", vbTextCompare) = 0 Then Call colcut
  Call pastcol
end sub

Sub Do_each_x_seconds()
 
SchedRecalc = Now + TimeValue("00:00:01")
       Range("Y110") = "Running"
       Application.OnTime SchedRecalc, "Sheet2.Update_val"

End sub

Hey,

I have this workbook that has 2 macros that are running simultaneously and I'm looking to run macro 1, then macro 2

The one macro will run if there are any changes made to the worksheet (a message will pop up if the sum of values (=A1+B1; =A2+B2;...) in a column (C) is greater than 0)

Another macro will change the original values (in column B)

While running the 2nd macro, value B1 will change, then the 1st macro will run saying, "Values in C2, C5, C6 are greater than zero". After pushing "OK", the 2nd macro will continue and fill in the value for B2, then the 1st macro will run again, saying "Values in C5, C6 are greater than zero"......

Is there a way to have the 2nd macro run fully through it's cycle, and then have the 1st macro run afterwards???


Thank you and I appreciate your time and efforts,
Bob

Hi,

I am facing the following problem. I have setup a macro in a file and, with slight changes, replicated this for 4 different files. So each workbook has the same macro pretty much.

Two of the workbooks refer to the same "line" and each one works on a different "direction" (south/north for example).

What I did, for matter of convenience is to create another workbook and place some nice buttons that when you click one of them it will open two of the workbooks and run the macro that is included inside them.

The problem is, this takes forever!! When I open each workbook individually and run their macros they take a reasonable amount of time, whenever I attempt to open them through another workbook and call the macros it takes forever!. I tried calling only one workbook and running 1 macro but it still takes much longer than opening the file manually and running the macro manually from within the file.

Any ideas why that happens? I can post the code if needed but it's nothing special, just opening the xls and running the macro.

I am calling the following macro from within another macro. When the macro reaches to syntax to call for the following macro, I receive a compile error message. How do I fix?

Sub SolverSyn()
'
' SolverSyn Macro
' Macro recorded 11/2/2009 by
'

'
SolverOk SetCell:="$J$19", MaxMinVal:=2, ValueOf:="0", ByChange:="$F$16:$G$17"
SolverSolve
End Sub

Thanks.

Hi all,

I am having trouble running a macro.

Everytime I click to run it, it runs another macro instead.

Originally they had similar names, "drag_formula" and "drag_formula1", so I thought that maybe why it is getting confused. But i have now changed the name of the macro I am trying to run from "drag_formula1" to "Part2", but it is still running the wrong macro!

I've tried closing down and reopening but i'm still faced with the same problem.

Does anyone have any ideas?

Nicko

I have the following macro that I am trying to adapt to work with my user
form. The user form is to prompt for a password to run a macro that unhides
a range of cells. I have not a clue what I am doing, but I am trying. Any
help would be greatly appreciated.

Sub Button1_Click()
If TextBox1.Text <> "1234" Then
MsgBox "Invalid password - you cannot access this"
UnHide.Hide
Exit Sub
Else
Range("E17:E50").Select
Selection.Font.ColorIndex = 0
Range("E17").Select
ActiveSheet.Range("A1").Select

End If
End Sub

Thanks
Mike Rogers

Hi All,

Thanks in advance for any help you can offer.

I have a situation where I am trying to run a macro in a different workbook using VBA. I want the macro to run within the workbook in which it sits. The following works:

Code:

    Application.Run "'Freed Blogs'!Main"

However it tries to run the macro main on the book I am calling it from when I want it to run on the woorkbook in which it resides. To add to my problems I ABSOLUTELY CAN NOT TOUCH the code in the other book as it belongs to another department. They have not specified any object variables so it means the macro is looking at my sheet.

Does Run have any parameters that you can force the macro to run in it's own book?

Is there another way I can do this?

Many thanks,

Fred!

I run a macro in Wkbk1, the macro uses the Application.Run command to
call a macro in Wkbk2.

The second macro closes Wkbk1. As soon as Wkbk1 is closed the macro
stops running.

Is there a way to stop this from happening?

Thanks in advance

Hi I have an interesting problem (I think at least). I have two macros. The first one gets a file name and worksheet name. Then it calls the second macro which opens up notepad (with the specified file name) and pastes the information in excel. The problem that occurs is when I call the second macro more then once in a row from the first macro. It runs great the first time but then problems occur as it tries to run the macro again. (Some times it just closes my excel file with prompting me to save). Calling any of the files works on an individual basis (I've tried for all three). I've erased some of the code due to privacy issues but path location is identical for all three files. Here is the code.

Code:

Sub Update()

Call Notepad("survey_fistp_en", "FISTP Data")
Call Notepad("mco_csrm_en", "MCO Data")
Call Notepad("survey_manager_en", "Manager Data")

End Sub

Sub Notepad(Name_of_File, Name_of_Worksheet)

RetVal = Shell("C:\WINDOWS\notepad.exe \\[Path location]" & Name_of_File & ".Asc ", 1)
SendKeys "^a", 10000
SendKeys "^c", 10000
SendKeys "%{F4}{TAB}~"
Workbooks("TLC Comment Reports").Worksheets(Name_of_Worksheet).Range("A5").PasteSpecial Paste:=xlPasteAll

End Sub

Hi,

I am trying to setup procedures that perform some steps then run
whichever macro is passed to them as a string argument

the actual code leaving the STRING variable containing the macro and
its arguments blank is:

Sub SendMacroName()

UseMacroStringToRunMacro STRING

End Sub

Sub UseStringToRunMacro(STRING)

....PERFORMS SOME STEPS...

'runs the macro represented in STRING
Application.Run STRING

End Sub

This works fine in all but one case: when the macro to be run is in
another file and has arguments. the actual string value that would work
in such a case:

Application.Run "FILE CONTAINING MACRO TO RUN.xls!MACRO TO RUN",
"ARGUMENT 1", "ARGUMENT 2", etc

i can give STRING that exact value by either of the following code, but
neither work on the Application.Run STRING command

STRING = """FILE.xls!MACRO""" & ", " & """ARGUMENT 1""" etc
or
STRING = """FILE.xls!MACRO"", ""ARGUMENT 1"" etc

both result in the error message: method 'run' of object application
failed


if the macro to run were in the same file as the macro calling it
[UseStringToRunMacro] the following would work:

STRING = "'MACRO_NO_FILEPATH, "ARGUMENT 1", ETC '"

[notice the string is bracketed by single quotes]
but if the macro is in another file this doesn't work [and it is NOT
b/c the file containing the macro isn't referenced, i tried
"'FILE.xls!MACRO, "ARGUMENT 1", ETC '" [that produces a macro cannot be
found error]

if the macro to run were in another file but had no arguments the
following works:

STRING = "FILE.xls!MACRO"

my guess is it has something to do with extra quotation marks around
the string, or how to represent the file name when the macro has
arguments. i think what may be happening is the STRING is interpreted
as being the macro to run not as encompassing the name of the macro and
each of its arguments.

Thanks for your help,

Ian

I need to create a macro to simulate 10000 binary vectors where marginal probabilities p1=.01 and p2=.04 and correlation=.27. I have no problems simulating the vectors using the random number generator but I have problems ensuring that the correlation is 0.27. This macro looks okay to me but it runs forever. Do I need to use Cholesky to simulate the binary vectors?


Code:

Dim f As Double

f = 1

While (f < 0.00265 Or f > 0.00275)

    Cells.Select

    Range("A1:G10000").Activate

    Selection.ClearContents

     Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$A$1:$A$10000"), _

        1, 10000, 3, , 0.01

     Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$B$1:$B$10000"), _

        1, 10000, 3, , 0.04

     Application.Run "ATPVBAEN.XLA!Mcorrel", ActiveSheet.Range("$A$1:$B$10000") _

        , ActiveSheet.Range("$D$1"), "C", False

        f = ActiveSheet.Range("$E$3")

Wend    

End Sub

I've run accross a problem where I would like to be able to refrence
data i've named in a macro that i'm running within a macro either by
having the macro output into the second macro or refrence a name in a
macro, is this possible?

Sub Macro1()


book = Range("B10")
booknum = 1
Path = Range("B11")
Sheets("data").Select
If Range("A52") <> "" Then
Application.Run "JEUpload_Template.xls!CreateSheet"
Range("A1:L1") = Heading
Range("A2:L51") = Sheet02
Sheets("Sheet2").Select
Sheets("Sheet2").Move

ChDir Path
ActiveWorkbook.SaveAs Filename:= _
book & booknum, FileFormat:=xlText, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
booknum = booknum + 1
End If

End Sub

Hi everyone,
I tried the following macro, self explanatory I would have thought, but the row is deleted whether the cell matches or not. Any ideas, please ? Quote:

Sub Button1_Click()
If a1 = a6 Then
Range("a6").Select
Selection.EntireRow.Delete
End If


End Sub

I'm new to macros, and have created them, so far, only through the
"record macro" function. My problem macro performs a "save as" and then
(supposedly) closes the sheet. When running the macro I find I need to
manually intervene twice, once for the "file exists - overwrite?"
dialog, and again for the "save changes to?" dialog. Is there a way, in
the macro, for me to respond to these queries? I responded during the
"record" but it didn't seem to take.


Thanks.

JLOB

I've got a macro working that updates things in different sheets, the sheets have peoples names in (this can't be changed).

When running the macro its fine, but if a new person is added i would need to update the macro for every sheet (the macro can be run from every sheet within the workbook).

I've noticed if you right click the sheet button and go to properties (or something) that it shows (Name) Sheet2 and Name Joe Blogs.

Instead of the macro calling to Name i want it to call to (Name), is this possible?

The code that calls the sheet looks something like this:

Sheets.("Joe Blogs")

Instead of looking for Joe Blogs i want it to look for Sheet2

Is this possible or am i just wishing on a dream?

i have a macro that read a workbook of 10000 line. it reads one by one row and then do some arithmetic and then append a new data in a new column in the workbook. When i run the macro, it takes about 30 mins to run. the excel 'freezes' when the macro is being ran but i know in background the macro is looping in all the rows. i have in my macro too Application.screenupdating=false

How can i put some sort of indication of what line the macro is manipulating without slowing down the macro? can i put a msg in the status bar? like row 1 being done, row 2 being done etc....

i want to know if i look at the excel after 15mins, i know which row the macro has reached so that i have an indication when macro will be completed.

I have a Userform which posts data to another WorkBook.( A network sheet) A macro on that workbook runs after being called from the Userform
Private Sub cmbDataEntry_Click()

What I'm trying to do is to have a macro that's running on the remote sheet to call a macro that's in the Userform that originally made the call to the remote sheet.

I tried calling the macro on the userform like this.

Application.Run "'Monday-form.xls'!Private Sub AfterEntryCleanUp"

This macro never gets called.

Do I have to include the name of the Userform XForm2010. If so where and how?
What is the correct format for calling a macro on a Userform? This macro clears all the entries / textboxes etc..etc.. on the Userform. I cant do it before I execute the code on the network sheet, because the values are still needed from the Userform.

Thanks

--------------------------------------------------------------------------------

Below is a macro I use in one of my worksheets however if you execute the macro with no file name is located at 117,5 it returns a microsoft visual basic error. Is there anything I can add so when the macro is executed if the cell is blank it retruns a box indicating you to input a file name in the above cell to continue. is this possible kinda like a validation for a macro.


Code:

Sub 1()
'
'Sub 1'
'
    Dim filevalue
    filevalue = Cells(117, 5).Value
   'L'
        Range("k118").Value = "=" & filevalue & "!K95"
    'R'
        Range("k119").Value = "=" & filevalue & "!K96"
    'C'
        Range("k120").Value = "=" & filevalue & "!K97"
    'K'
        Range("k121").Value = "=" & filevalue & "!K98"
    'S'
        Range("k122").Value = "=" & filevalue & "!K99"
End Sub

Hi, currently use this macro (see below) but occasionally I have more rows than 842. I could increase to 10000 and this would work but looking for a more automated macro that will automatically remove duplicates from all rows i.e. a macro that works out all rows.

Sub copyTab()
'
' copyTab Macro
'

'
Cells.Select
Selection.Copy
Sheets("filtered_data").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$J$842").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Hi all,

Am using the program where am calling the other macro and after that macro and based on the result i need to run the remaining steps of macro1

Sub macro1()

'some code here

Call step2 'calling another macro

'some code here

End Sub

My problem is the step2 macro takes some 20 seconds to finish the run. so i want to resume the macro1 for 20 seconds and then start running the secong part of code.

Can anyone help me to solve this issue....
thanks in advance....

Two-part question

I know there is a way to prevent events (such as worksheet_change) from
activating from a running macro. Eg. A user enters a value in "A3" and it
sets off macro. But a Macro changes the value in "A3" and no Macro is set off.

Question one, how is this done through VBA options.
Question two, Is there a way to set this option on/off programmatically.