Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Will Running A Macro Within Another Macro Use Variables?

Will running a Macro from within another macro allow the second macro use the first one's variables?


Macro A Starts

Variable x set

Macro B starts

Macro B uses x from Macro A

Macro B ends

Macro A ends

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Running A Macro For A Matrix Of Variables
I have a matrix that has variables in the rows and in the columns. Then I have a regular table. For each row in the table, I need to run a macro using the combinations in the matrix. I just don't know how to combine these two to make my macro run appropriately with the right combos for the right amount of time.

View Replies!   View Related
Macro Stops Running The Macro Completely And Hangs In The Middle
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.

They are four PC's along with our Managers laptop that run this Macro daily.

About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.

We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.

I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.

I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.

View Replies!   View Related
Running A Macro When A Value Is Reached In A Specific Cell Used To Call Up Macro
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.

View Replies!   View Related
Macro Running In OS X, Able To Run This Macro On 11 Files At Once
I have this macro that a friend of mine wrote on a windows machine, and I can't quite figure out the code changes needed for OS X. For someone who knows how to do this, I'm sure it would only take a second, but I've been banging my head against the wall on this thing for too long.

Option Base 1
Sub Data_Compiler()
Dim FileSelected
Dim FileName
Dim CompiledDataArray(1 To 30, 1 To 53) ' Array to store our statistics
Dim Counter1 As Integer
Counter1 = 1
FileSelected = Application. GetOpenFilename("Your Files,*.xls", , "Select Files", , True)
If StrComp(TypeName(FileSelected), "boolean", vbTextCompare) = 0 Then Exit Sub
For Each FileName In FileSelected
Application.DisplayAlerts = False................

I can get a "open files" dialogue box to pop up, but I can't figure out how to make sure that multiselect is enabled. I need to be able to run this macro on 11 files at once.

View Replies!   View Related
Running A Macro
Can i run a macro by just the pointer going over it.

View Replies!   View Related
Concatenate Macro With Variables
I'm trying to write a macro concatenate a report but the report outputs data on different rows when ideally I'd like it to all be on one row. The C column will have something in on the top line but blank for the rest until it gets to the next set of data.

Unfortunately it's not always the same amount of lines every time.

View Replies!   View Related
Variables In Ranged Name Macro
I am trying to categorized a dataset by country. The dataset need to be updated everyday and the size of each category is different each time. So I try to define a ranged name using variables.

Dim rows_CN As Integer
Dim star As Integer
Dim endn As Integer
Dim lookup_cn As name
Dim lookup_HK As name

ActiveCell.FormulaR1C1 = "= COUNTIF(criteria!R37C2:R500C2,""CN"")"
rows_CN = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(criteria!R37C2:R500C2,""HK"")"
rows_HK = ActiveCell..................

View Replies!   View Related
Confirmation Box Before Running A Macro
How do I ask for a confirmation box before running a macro, but only one time. What I mean is some of the macros I use loop and it wouldn't be practical to click on "ok" for each time the macro ran.

So again I just need some code to tack on to the beginning of a couple of my macros that will ask me before they run but only once.

View Replies!   View Related
Running Macro On Close
I'm trying to run what is a pretty basic macro when a file is closed

Sub Auto_Close()

FileCopy "C:Documents and SettingsdaveDesktopWorking olderPlanner.xls", "S:LeavePlanner.xls"

End Sub

I've checked that the Auto_Close feature is what I need, but it just doesn't seem to be running

I've tried it from clicking the x, and also File>Close, but with the same lack of success

View Replies!   View Related
I am trying to achieve an automated macro.

I would like my spreasheet to look at cell B2, compare this to a list of information in Sheet2 and if it matches, run Macro1

View Replies!   View Related
Running A Macro From A Different Workbook
I have 2 workbooks (Workbook1 and Workbook2).

Workbook 1 contains a button, that calls a macro. This macro calls a macro from Workbook 2.

For this, I am using the Application.Run function.

The Macro in Workbook2 is a simple one line macro that updates cell A2 to a value of 6.

Range("A2").Value = 6

This works fine!

However, I want the the cell A2 in WORKBOOK1 to change to 6, not the cell in Workbook 2.

To add an element of difficulty, Workbook 1 could have any name (I won't know what it is, as the user could change it).

How can I get the Macro in Workbook 2 (Called from Workbook1), to update the cells in Workbook 1.

View Replies!   View Related
Running Macro Automatically Using VBA
Is it possible to run the following macro automatically using VBA when cells B2 and C2 equals "Yes"? I don't want any manual intervention for this to happen.

Sub clearcells()
' clearcells Macro
' Macro recorded 14/10/2009
End Sub

View Replies!   View Related
Constantly Running Macro
I am using

Sub FillColor()

With Application.FileSearch
.LookIn = "K:Departmental FoldersAccountingMonthly Accounting Package20074_2007Green BookHighlights"
.Filename = "110 Summary Income Statement-Month, YTD"
If .Execute > 0 Then
Dim rFoundCell As Range
Set rFoundCell = Columns(1). Find(What:="110", After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
rFoundCell.Interior.ColorIndex = 35
End If
End With

End Sub

to highlight cells if a file is found in the system.

These files are saved by other users and I would like this macro to be "running in the background" while I have the file open so if someone saves a file it will automatically update. I don't want to start the macro every time I want to know if a file is saved. Can this be done?

View Replies!   View Related
Running A Macro From A If Statement
Is it possible to run a macro from an IF command?

View Replies!   View Related
Macro Running With Columns From A To H
I have a macro running with columns from A to H and ''infinite'' rows
+conditionnal formating and one formula for the value in column D
I'd like to add one more condition, but I can't figure how...

*I'd like to add a step to the macro on sheet1 to automatically move a whole row (a to h) to sheet2 as soon as my equation in column D find a value = 0

after each row is transfered, i'd like to sort it in a descending order on sheet2 based on the number value in column A

what to add to get this to work

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'Only run if 1 cell is changed
If Target.Column 8 Then Exit Sub 'Only run if change is in Column "F"
If Target = Cells(Target.Row, "A") Then 'Check to see if entry matches column "A"
MsgBox "Invalid entry"
Application.EnableEvents = False
Application.EnableEvents = True
End If
Range(Cells(2, 1), Cells(Target.Row, 8)).Sort Key1:=Cells(2, 8), Order1:=xlDescending
End Sub

View Replies!   View Related
Slow Running Macro
Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.

The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.

View Replies!   View Related
Macro Running Slow
I have a macro that isn't very complex but it is running very slow, does anyone know any tricks on how to speed it up?

View Replies!   View Related
Macro For Running Solver ...
I have a problem with a macro in Excel 2007. It invokes
solver and creates an answer report. Please find the VB
code for macro, below.

View Replies!   View Related
Macro Running Very Slow
I have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?

View Replies!   View Related
Running Macro From Ruby
I have a problem which involves calling an excel macro from another application in this case, ruby.

$excel.Run "ActionCommands.xlsm!CreateDynamicDataSources"

Here is the simplified macro that it calls

Sub CreateDynamicDataSources()

MsgBox "Hello"
ActiveCell.Formula = "Hello"

End Sub

The good news is, the macro is indeed being called because the MsgBox is executing. It may even be able to read cells and send the data back to ruby or display it in a message box. The bad news is I cant select a sheet or cell and actually write to it! When I call this macro from Excel it works as expected. How do I fool Excel (2007) into thinking its OK to allow these operations. There is no explicit message that says Hey this is in read only mode but I suspect that is what is going on.

View Replies!   View Related
Running Macro In Another Workbook
I have 2 files in a folder, "Main.xls" and "My Database.xls". "My Database.xls" contains the following macro in 'Module1':

View Replies!   View Related
Macro To Stop Running At Last Row
I have a spreadsheet containing a macro to automatically sum values from week to week and display the max/min and average. This is almost fully working. The problem I have is the macro copies the sum formula down the page and doesn't stop at the last row. It always adds one or two extra rows. The formula should stop at row 77 however this does not happen and therefore the sum keeps going. Which in turn, distorts the max/min figures.

View Replies!   View Related
Running Macro Every 20mins.
I already have a macro made named (PowerPoint) that is saved to the wookbook. I am looking for a way to have this run every 20min. I have tried several differnt things and it did not work. Here is one I found online. I only need it to run when it is open and every 20mins. as long as I have it open.

View Replies!   View Related
Running The Same Macro For Different Workbooks
I was able to create a macro that formats the worksheet that I am working on. However, I would like this macro to be available to all worksheets that I would be downloading from a system.

I tried using the personal macro workbook, but when I ran the macro, it formats the personal worksheet and not the one that I would want to format.

I used general codes, but it pasted the formulas onto the personal.xls file.

View Replies!   View Related
Running A Macro Every Tenth Of A Second
I am trying to run a Macro every tenth of a second. The usual method (with "Now" and "TimeDate") cannot do that; I found on the website that one can use "Timer" but I did not manage to make it work.

View Replies!   View Related
Macro Running Sql Query
I want to write some VBA that retrieves data from a lotus notes database into excel. I will then have some other code that manipulates the data for the user. I have managed to do this with MS Query manually. However when I start to put into VBA my problems start. As I do not know Sql I use the macro record function. This worksso far so good. The problems seem to start when I re-run the macro and try to save the workbook. Excel just sits there with the CPU running at 99%. I left the pc for an hour and it was still in the same condition with the status bar showing save.

Sub Macro4()
With ActiveSheet.QueryTables.Add(Connection:= Array(Array( _
"ODBC;DSN=premax;Database=Premaxse000273.nsf;Server=local;UserName=John Cross/bsp;EncryptMaxSubquery=20;MaxStmtLen=4096;MaxRels=20;M" _
), Array( _
"axVarcharLen=1024;KeepTempIdx=1;MaxLongVarcharLen=1024;ShowImplicitFlds=0;MapSpecialChars=1;ThreadTimeout=60;" _ .............................

View Replies!   View Related
Running Macro After A Specified Time
I have a macro that would run once the workbook is opened. This macro would then open another excel file to get updates then close it. Now this file is also being accessed by other persons in the network but just for a split second. Since an opened file would result into an error, what proper codes should I add so that if the file is currently in use, the macro would then run again after 5 seconds and would do the same thing until the file is ready? This is what I have so far. All in the standard module.

Sub Updater()
Dim updatePath As String
updatePath = Worksheets("Entries"). Range("E104")
Application. ScreenUpdating = False
Workbooks.Open (updatePath)
On Error Goto Errorhandler
Workbooks("APTupdater.xls").Close True
Application.ScreenUpdating = True
Exit Sub


View Replies!   View Related
Copy And Paste Variables Using A Macro
I would like to use a macro to do the following...after selecting a cell eg C12 the macro would copy its contents and paste them into S12. However I would like it to be able to do this with any row I select and column "S" contains links to another worksheet, for example... C12 contains 123456, S12 contains =654321!C41.

I need the numbers to be the same in both cells. So after the macro has run C12 would still be 123456 and S12 would now be =123456!C41. The numbers in the cells and the rows are variable (columns are always the same, "C" & "S").

View Replies!   View Related
Pass Variables Between UserForm & Macro
I am looking at using forms, as in the Userform... Not used them before, but would like to know, if you create a text box on it, is the value global, so any module can use the var? or how can I, so that value entered can be used on the whole book ?

View Replies!   View Related
Using MAcro Recorder To Store Variables?
Is it possible to use the Macro recorder in Excel to store user variables that they put in through a UserForm? So say when a textbox on a userform is changed, it records a macro of what is put into the textbox, which it can then call to retrieve the value.

View Replies!   View Related
Macro Suddenly Running Slowly
So I have a macro which is running slowly, though I can't figure out why. I don't remember adding/subtracting from it. All code prior to this macro being called runs fine...So this part below runs fine:

Private Sub Step_3_Click()

Dim YR As String
YR = Range("C24")

Dim DT As String
DT = Range("C27")

Dim MNTH As String
MNTH = Range("D23")

For Each wb In Workbooks
If wb.Name = "Top100gen2start.xls" Then
MsgBox "You need to close the Top100gen2start.xls file before
proceeding.", , "Close Top100gen2start.xls file"
Exit Sub
End If

If CheckBox1.Object = False And CheckBox2.Object = False And
CheckBox3.Object = False And CheckBox4.Object = False And
CheckBox5.Object = False Then
MsgBox "You didn't select a sector report to run!", , "Select a
sector report"
Exit Sub
End If

If CheckBox1.Object = True Then
Call FullBlown
If gameover = 1 Then
Exit Sub
End If
End If

Once I call that Fullblown macro (or any of macro similar to it in this process), the whole things slows WAY down (I mean as soon as it is called)...Not sure what I'm doing wrong, but here's the called macro:

Public Top100Check As Integer, gameover As Integer

Sub FullBlown()

Application.ScreenUpdating = False

' Dim x As Date

' x = Format(Date, "mm-dd-yy")

On Error GoTo Jump
' Workbooks("PERSONAL.XLS").Close

Dim YR As String
YR = Range("C24")

Dim DT As String
DT = Range("C27")

View Replies!   View Related
Running A Macro On A List Of Worksheets
I have a workbook that contains a worksheet called CALC , in column A of this worksheet I have a list of other worksheets that I would like to run a macro on.

Would it be possible to set up some code that would select the worksheet at the top of the list, perform a macro then select the next worksheet in the list and carry out the macro on that sheet until all worksheets that appear in the list have been updated.

View Replies!   View Related
Dialog Box To Confirm Running Macro
i have a macro button designed to wipe all user entered data into a workbook. I want it so you cant accidently press (as you cannot undo the operation). Anyway for making a dialog box appear asking if you are sure to wipe date, with a yes or not option.. no will obviously abort the macro.

if possible, another box to come up asking if you want to save the file first with a different file name (comes up with a save as box).

this is the code so far:

Sub MonthCleaner()
' MonthCleaner Macro
' Macro recorded 17/10/2005

Application.ScreenUpdating = False

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("H10").Select .........

View Replies!   View Related
Running A Macro At A Specific Time
I want to know if I can schedule a macro to run at specific times?

If so would 6 requests at the same time slow excel down?

View Replies!   View Related
Browse For Worksheet And Running Macro
I am trying to create a program that will allow the user to browse for an excel worksheet and execute a macro that will format the worksheet with a click of a button.

Here is an image of what I want the template to look like:
Picture 2.jpg

Now I need to know what language I should use and the codes to make it run.

View Replies!   View Related
Prevent Macro From Running If Cell Is Red
I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.

View Replies!   View Related
Check Cell Before Running Macro
I need to check cell G5 (which is G4-G3) before running a macro. if G5 is zero i need to pop up a message to inform the user to enter data in cells G3 & G4. And if G5 is greater than Zero the macro shud run.

View Replies!   View Related
Prevent Private Sub Macro From Running
I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.

View Replies!   View Related
Name Range Is Not Saving When Running Macro
Thanks for all the help so far with this issue. I seem to be getting errors after running a macro a second time. The name of the cell range is changing. I have attached the spreadsheet and I will try to be specific as possible.

Goal 1: I need to input monthly data into the Sales tab and then save it as a CSV file.

Goal 2: Re-open original xls file that has data then Run macro to create reports.

Goal 3: Save Original xls file as a clean sheet to use again next month.

1 - Insert Data into Sales tab
2 - File/Save As a CSV (Now the CSV file is open)
3 - Close CSV file and re-open the original xls file
4 - Run Macro to create reports
5 - Print Reports
6 - Manually delete current data on all tabs so I have a clean sheet for next month
7 - Save and Close


When I open the xls file (next month) to input new data and run the reports I get a Run Time Error '1004" The Pivot Table field Name is Not Valid

I found that the Named Range of cells changed from the original:

View Replies!   View Related
Data Format When Running Macro
I am having a problem with a macro which has only started occuring on Excel 2000. It worked fine on Excel 1997. Basically, the Macro converts a text file (with comma delimiter) to an excel format. The problem I am having is with some of the date fields in the text file.

All dates in the Text file are in the format dd/mm/yyyy (i.e. 01/02/1990)

If I convert the Text file to Excel format manually through the wizard it correctly converts the date in Excel to dd/mm/yyyy (01/02/1990). However, If I run the Macro I have created, it converts the date to mm/dd/yyyy (02/01/1990).

This only seems to happen if the dd field is less than 12.

I attach a sample of the text file and Excel file (with incorrect dates) and also the Macro.

Basically I need the Macro to convert the dates in dd/mm/yyyy always....

View Replies!   View Related
Confirm Selection Before Running Macro
I have an excel form where users can input modifications by selecting a range and then pressing a command button to input a value in the selection.

My goal is: to restrict the location where a user can select a range of cell and then execute the macro . The cells where the user can use the macro are merged and in my code, I used the address as show in the address bar in excel for their location. If the user select a cell that is not permitted, a message box is displays and exits the code.

If (Selection.Address <> Range("AE7").Address) _
Or (Selection.Address <> Range("BM7").Address) Then
Msgbox ("Wrong location") Else

For some reason it doesn't work since the message box appears even if I am within the permitted locations.

Note: AE7 is the first cell of a merge, same as with BM7

View Replies!   View Related
Running A Macro From A Batch File
Is their a way of creating a batch file which will open an Excel workbook and run a single macro?

View Replies!   View Related
Show Time Of Running Macro
I have a macro which downloads data from website. But takes some more time to complete a work. I want that a time should display at the bottom bar of excel where "Ready" is written. THe time should be in format.

View Replies!   View Related
Import Macro - Getting An Error When Running It
I tried to adapt this code into my own from another thread. [url]

BUT, I am getting an error when running it. On the "For i" line. And I know just enough VBA to be dangerous. Can someone help?

I have another code setup to run if a certain cell equals "October" then it calls ImportData. Then this code is supposed to pull all files out of My documents/TechConnect/October08 and place the data into sheet 1....

View Replies!   View Related
Sheets Are Flickering Even No Macro Running
Some of my sheets are flickering even when there is no macro running. It blinks and flashes from time to time. It does not affect the funktion in the sheet except that its annoying as hell. Some times I have to scroll down and back up to "clear" the screen from the flicker/flash effects.

I can open an old file fresh and it flickers and flashes from start. It doesnt matter if I start it with or with out macro activated.

View Replies!   View Related
Progress Bar To Work While Running The Macro
I was looking at a previous post to get a progress bar to work while running
a lengthy macro. Below is the post on how to incorporate the progress bar
into your code.

That works fine expect for one thing. There is a cancel button on the progress bar, and when it is pushed the rest of my code continues running. How do I end my code if I press cancel on the progress bar?

Sub Main
Dim PB as clsProgBar
Set PB = new clsProgBar

'if your subs are being called from a userform, hide the form first using
With PB

.title = "some title"
.caption1 = "Executing, this may take a while"
.caption2 = "Doing task 1"

View Replies!   View Related
Running Macro On All Worksheets In A Book
I have a macro that I would like to run on every worksheet in my book (over 100 sheets wide). As it takes about 20 secs to run on each sheet, and I don't want to click from sheet to sheet, how can I make it run on every sheet in the book? And no, they aren't named sequentially, either alphabetically or numerically.

is there a function in Excel to return the number of sheets in a book?

View Replies!   View Related
Running A Macro When A Certain Value Is Found Anywhere In A Certain Column
Im working in Excel 03 with a large table. Im hoping to run a macro against a cell with value X whenever that value is found within a particular column. Id like this to be relative and not tied to a particular column so I can tweak it as needed down the road and run it against different columns.

In the example below, lets say Im working with column B. Id like something that would go through every cell in the column and run a macro when it comes across a particular value. Lets say this value is x.

__A B C D E F
1 a x a a a x
2 b a b b b a
3 c x c c c c
4 f f f x f f
5 s s s a s s
6 g x g g g x
7 a a a a a a
8 s s s s s s

So, in my example, the macro would be run on B1, B3, and B6. And, if I wanted to go back in later, I could run this on column D and the macro would run on cell D4.

View Replies!   View Related
Protection Stops Macro From Running
I want to make the worksheet protected, however, when I protect the worksheet the macro will not run.

View Replies!   View Related
Work On Other Files While Running A Macro?
I have designed a macro which consists of a form through it takes the input into excel sheet. The macro is running fine and I dont have any problems with it. The problem I am facing is that after I start running this macro, I am not able to access any other open excel files. Is there any option to make all other excel files available during running a macro?

View Replies!   View Related
Copyright 2005-08, All rights reserved