Calling Another Macro

Aug 4, 2006

I am trying to call another macro, but instead of using the name of the macro, I have it in a variable. I am getting an error that says "Compile Error: Expected sub, Function, or Property." Can you call a macro that is in a variable? I basically have a bunch of macros created. I also have a list of the name of those macros on a worksheet in Excel. I want excel to start at the top of the list in excel and run the macro, then move down and run the next macro, and so on. Below is my code.

Sub Commercial_2005()
Dim Macroname As String
Workbooks("bleeg.xls").Activate
Worksheets("CommercialList").Select
Cells.Range("a1").Select
While ActiveCell.Value <> ""
Macroname = ActiveCell.Value
Workbooks("copy of recast_Report_v2.xls").Activate
Call Macroname
Workbooks("bleeg.xls").Activate
Worksheets("CommercialList").Select
ActiveCell.Offset(1, 0).Select
Wend
End Sub

View 3 Replies


ADVERTISEMENT

Calling Macro From Add In

Feb 12, 2009

I have created an XLA using a blank workbook which has a macro (called CompareMan) I have written within it.

I have written a utility (called INSTALLMAN) to install this add in.

When I run the install it creates and activates the add in OK.

The install also creates a toolbar with a button to trigger the macro.
Problem is, when I click the newly created button, no matter which workbook I am currently in I get a message ...

"The macro INSTALLMAN.xls!CompareMan cannot be found".

So my question is, how do I get my button to trigger the macro in my add in?

View 14 Replies View Related

Calling Macro From Different Workbook

Nov 14, 2006

I have a set of eight files, each of which is refreshed daily through a macro. I wanted to set up a master macro in a separate file that would open up each of the eight in turn, calls its refresh macro (which saves and closes the workbook), and then open up the next one, etc. I've searched the board and Help on this subject, but I keep getting an error:

Sub UpdateCSHoldReports()
' A sub to update all the US CS Hold Reports

' First, change the directory to the one that holds the files

ChDir "s:Marketing_ReportsCustomer Service2006CS Hold ReportsUSA"

' Next, open each file in turn, and run its refresh macro
' The refresh macro saves and closes the file automatically

Workbooks.Open ("VERIFICATION HOLDS 1000 PLUS.xls")
Workbooks("VERIFICATION HOLDS 1000 PLUS.xls").Activate
ActiveWorkbook.Sheets(1).Activate
Application.Run ("VERIFICATION HOLDS 1000 PLUS.xls!V1000Refresh")
End Sub

I'm only working on one file right now; I'll add the others when I get this one going. The error message I get is "1004" "The macro VERIFICATION HOLDS 1000 PLUS.xls!V1000Refresh cannot be found." It occurs when the Application.Run statement attempts to execute.

What obvious thing am I missing here? I have checked all the spellings, etc., so it's not something like that.

View 9 Replies View Related

Calling A Macro In Another File

Nov 19, 2009

I'm writing some code in a "control" Excel file that will open a series of other files and successively run some processes. Each of these files has its own unique "refresh" macros, with parameters that vary from file to file.

How do I put code in my "control" file that will, when it opens each of these other files, run the "refresh" macro that is contained within them, and not the "control" file itself?

View 9 Replies View Related

Calling Macro's From Another Workbook

Nov 26, 2009

I have been combining multiple macro's into one large Macro and after research, it seems that only the call function works without any hitches.

Is there a way to use the call feature in one workbook while the macro's themselves, 5 or 6 of them, are in another workbook that is closed?

All users would have access to both workbooks.

The reason I am trying this is because I do not want all the other users to have to choose between multiple macro's and some of my colleagues like to use a radio button to link to the workbook. so it needs to have one file in it...

View 9 Replies View Related

Suppress MsgBox When Calling Macro From Another Macro

Jan 9, 2012

I have a workbook with two macros that the user can run individually. They unfortunately take a fair amount of time to complete (approx 1 hour each) so I implemented some timing related code that at the end of the macro completion, it notifies them with a msgBox indicating how long it took to run. With that said, I would like to allow the user to run a macro that simply calls the other two so that they can kick it off at the end of the day and they both will be finished by morning. I created a new macro that simply makes a call to each of the other macros individually. The problem is, my msgBox that displays after the first macro completes, waits for "OK" acknowledgement which defeats the whole purpose of being able to run them both via one macro. I have tried to include Application.DisplayAlerts = False prior to calling the first macro but it is not working. Here is an example of what I am trying to do.

Sub runAllMacros()
Application.DisplayAlerts = False
Call compileBookData
Call compileLaborData
Application.DisplayAlerts = True
MsgBox "All Rollup Macros have completed execution."

View 5 Replies View Related

Macro To Select VbYesNo When Calling Another Macro

Apr 20, 2008

I have a workbook with a 2 macros "PopulateSheetlist" and "SaveEditedversion"

Normally they are each button operated, and prompt the user with vbYesNo style options. I wanted to write a Macro, say "Macroautomatewkbk that would automatically answer the prompts.


Sub Macroautomatewkbk ()

Call PopulateSheetlist

' Always answer YES or OK to any prompts this macro may offer

Call SaveEditedversion

' Answer No for the 1st prompt and YES for the 2nd prompt

End Sub

MsgBox "Both macros "PopulateSheetlist" and "SaveEditedversion"

' Some error handler here, don't know how this should work exactly.
Could any one please explain how to fill in code in the commented sections in the above sample code?

Edit: I tried recording a macro to do the above, but it only showed the zooming and scrolling that occurred, none of the button prompts being answered.

View 9 Replies View Related

Calling Macro Numerous Times?

Jan 24, 2014

At the moment I have a macro that is assigned to a button. When clicked, it creates a newline, create and copy a column in another sheet. This code works fine :

[Code] ......

What I now want to do is make a cell called "Total Number of Sites" and allow user to input data into "D3" manualy. eg if 3 sites, user input 3 in D3 so D3 = 3.

Since total number of sites = 3, Instead of clicking that button I made 3 times, I want to call the macro 3 times using a nother button.

However it doesn't work. This is what I used:

[Code] ..........

View 5 Replies View Related

Calling An Image Alt Text For Use In A Macro?

Aug 19, 2009

I'm hoping there is some way to call whatever text is stored in the "alt text" field of images placed in an excel sheet for use as a variable / string reference in a macro.

The "alt text" will not be changed in any way by the macro.

View 10 Replies View Related

How Do I Obtain The Name Of The Form Calling Macro

Nov 21, 2007

I've assigned a single macro to numerous Form Checkboxes (not ActiveX). I'd like the macro to be able to identify the Checkbox calling it, so that if for example, if it were fired by clicking "Check Box 41", the macro should know that.

Is this possible, or must I use an ActiveX control?

View 9 Replies View Related

Return From A Macro To The Calling Worksheet

Jan 6, 2009

how I can go back to the worksheet I was in before I branched off to work in another worksheet via a macro?

I know you can tell the macro which worksheet to go to, but not sure how to get vb to remember where I was and return to the same sheet (or even cell).

View 9 Replies View Related

Looping Through Each Worksheet And Calling A Macro

Jan 13, 2010

I had create a few macro with the macro name the same as the worksheet name.

How can i create another macro to loop through all the worksheet and if there is any data in Cell A1, it will call the corresponding macro. If there is no data, it will go onto another worksheet.

View 9 Replies View Related

Calling Macro: API Postmessage Or Sendmessage

Oct 4, 2006

Using a different script application, I would like to run my VBA macros while working in Excel. I was wondering if I could achieve that using PostMessage or SendMessage calls. Part of the problem is that Excel only seems to allow either Ctrl + letter or Ctrl + Shift + letter as an assignable macro shortcut. My script gives me flexibility in that I can assign any key or combination of keys to trigger any kind of routine, like a PostMessage call, for instance. So while working on my workbook, I might want to press, say, ScrollLock followed by a letter of my choice, followed by another letter of my choice (a sort of super shortcut), resulting in the running of a Macro of my choice in Excel.

View 5 Replies View Related

Calling Macro In Another Workbook And Carrying Some Variables Back And Forth

May 22, 2013

I have a macro running in a workbook that gathers some data (a date, a string and a few arrays). Towards the end of this macro, I need it to open another workbook and run a macro that sits in this other workbook, using the data from the first workbook. I then need it to return some results (several integers) back to the first macro to be pasted into the first workbook.

I gather that I can't use 'Call' as the second macro is in another workbook.

I've found that I can use Application.Run but I'm unsure how to carry variables back and forth using this.

How to move the variables between macros / workbooks using the Application.Run option, or maybe another way of doing things?

View 1 Replies View Related

Excel 2000 :: Macro Calling Unrelated Function?

Jun 9, 2014

the macro works fine until it executes the paste values. At that point, the macro jumps to the "CountThem" function which is located in another workbook. The data that I am copy/pasting is in no way connected to any cells that are using that function. Although, other values in the workbook are passed down from data that uses that function.

I am still in the dark ages using Excel 2000.

This is the code for my macro.

Code:
Sub Current_to_Raw()
'
' Current_to_Raw Macro
' Macro recorded 2/12/2014 by
'
'
Range("N14").Select

[code]....

View 2 Replies View Related

Calling A Macro And Formula Macro..

Aug 13, 2009

I created two macros. One for refreshing the data I pull from a database, and another one adding some formulas I need. On the formulas macro, I don't know how to have it automatically fill until the last row of data to the left of the column with the formula. (Just like if I double clicked it and it filled down automatically). I also tried to combine these two macros, but I get errors and they don't execute.

View 3 Replies View Related

Calling The Min Row To Another Row

Oct 22, 2007

i have 5040 rows in a sheet. in columns A,B,C,D,E,F, i have some input. and based on that i have some formula on column G.

lets say, min value is at row 4094 by looking at G column... i want to call entire row to the, lets say K L M N O P ....i need a formula for that....

random numbers are included in the formula. So ever time i make any changes on the sheet, min value changes even thought i dont play with the formula and input... so i want my new min value shown at row 1 of K L M N O P, every time i do that

how can i do that?

View 11 Replies View Related

Calling One Sheet From Another

Sep 26, 2009

I am creating a set of lists, each on a different sheet. I want to have the first sheet as a sort of "Title Page" with basic information from the others. Is it possible to create a link of some types so that I could click on "Magazines" for example and go to the sheet called Magazines, or "Books" and go to the sheet called books. The latter would be very handy as this workbook has around 30 sheets in it, which is a pain to look through to find the right one.

View 2 Replies View Related

Calling/using An Array

Apr 15, 2009

I have to do the same procedure for a number of profit centers. The only differences are the names of these profit centers. Therefore I want to use the same code, with changing references. How can I use this array so that it calls/uses 1 profit center at the time? If I use the following code they all are used at once (P1100P1200P1300P1500P1600P1900)

View 2 Replies View Related

Calling A Sub With A Formula

Jun 22, 2009

Is there a way to call a sub with out actually saying its name? In other words can I refer to a sub by a value or a cell that holds its name? for example, in my CNC programs I often use something like this;

View 14 Replies View Related

Calling UDF Inside VBA

Jan 20, 2012

Okay, I know you can reference worksheet functions with code like:

application.search()

But how do you use a UDF function within VBA???

I tried, thisworkbook.UDF() (UDF being the name of any UDF) but it didn't work.

View 3 Replies View Related

Calling A Form From Add-in

Mar 13, 2012

I have a add in for excel (self made) and I recently hit a wall when trying to add functionality to it.

I have a form "UserForm1" created within the .Xlam file of the add in.

I want this form to be useable for any excel file that might be open, so putting code in the excel file that needs to use the form is undesirable.

The functions carried out by my add in are activated trough ribbon buttons.

my goal is to have a button on the ribbon that loads the form.

As the UserForm1.show code doesn't work, I will need a alternative.

I did find the code for making the sheet show if there would be a button in the workbook, however I want all code to be in the add in.

View 4 Replies View Related

How To Loop Instead Of Sub Calling Sub

Mar 17, 2012

I am using the following code to find the text "Employment" in any row in colum H. If found it copies it to another worksheet.

Sub CopyRowsSht8()Sheet8.SelectSet Rng = Sheet8.Range(Sheet8.Range("H1"), Sheet8.Range("H" & Rows.Count).End(xlUp))For Each cell In RngIf cell.Value = "Employment" Thennum = cell.RowSheet8.Range("A" & num & ":" & "L" & num).Copy Destination:=Worksheets("Employment").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)End IfNextCall CopyRowsSht9End Sub

1. This sub calls CopyRowsSht8 and then this calls CopyRowsSht9 and so on. I have 15 sheets so how would I code this into some kind of loop to include sheets 8 to 22 instead of calling sub then sub then sub etc.

2. It takes a while to do just two sheets so 15 would be very slow - can this be made faster. The maximun number of rows containing data is only about 100 on each sheet -could that make a difference?

View 1 Replies View Related

VBA Calling Another Subroutine

Mar 14, 2008

Im trying to call another subroutine within the same workbook but it doesnt work,

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

Sub Macro1()

Dim mac1 As Variant
Dim mysub As Variant

Select Case Cells(1, 3)
Case mac1
Call mysub

End Select

End Sub
---------------------

View 9 Replies View Related

Calling Variables

Apr 14, 2009

The below thing has been driving me up a wall for 2 days now. I know the fix has to do with calling the variables, but I can't figure out the exact code.

Sub Parsing_Logic()
Dim IBG_URL As String
Dim A As String
A = Mid(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL), 8)
Dim B As String
B = Left(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL))
Dim C As String
C = Right(IBG_URL, Len(IBG_URL) - (Len(A) + Len(B)))
Dim D As String
D = Left(C, Application.WorksheetFunction.Find(".200", C))
End Sub

Function IBGLink(IBG_URL As String, Formatted_Date As String)
If Application.WorksheetFunction.IsErr(A) Then
IBGLink = (D & Formatted_Date)
Else: IBGLink = (B & Formatted_Date & D & Formatted_Date)
End If

View 9 Replies View Related

Calling A Function

May 1, 2007

I guess I just don't understand the basics of calling a function. I have the following function in a standard module:

Function CleanUp(ws As Worksheets)
ws. Range("A2:P100").ClearContents
End Function

I have this code in a worksheet module:

Sub Trying_to_call_a_function()
CleanUp (ThisWorkbook.Worksheets("Month End"))
End Sub

But when I run the routine that calls the "CleanUp" function, I get the message: "Object doesn't support this property or method." I have tried changing the Function to this: CleanUp (ws as Worksheet) [instead of (ws as WorksheetS)]. Yet, this does not change anything.

View 3 Replies View Related

Calling A Subroutine

May 23, 2007

Is calling a subroutine within another different than running it from the play button in the VB editor? I am getting different results.

I have the following code that I want to call from another subroutine that creates a series of command buttons.

Sub DynamicButtons()
Dim ButtonCount As Integer
Dim ctl As OLEObject

' Create the Button objects
ButtonCount = 0
For Each ctl In Sheets("Sheet1").OLEObjects .............

View 9 Replies View Related

Calling Columns Into Different Worksheets

Nov 4, 2008

I'm not sure whether this should be a general question or it should go in the programming section... but here here goes anyways

I need to create a spreadsheet where the contents of Col A & B in Sheet1 are copied into both Col A & B in Sheet2 & Sheet3. Sheet1 Col A&B will be continuously updated (rows will be added) and as these columns are updated, Col A & B in Sheet2 & Sheet3 should be updated automatically and keep the format that is set in Col A & B in Sheet1.

Now, Col C&D in Sheet2 & 3 will be updated by a different user. As the user updates the cells in Col C&D in Sheet2 & 3, Col C&D in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet2 (and keep the format) and Col E&F in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet3 (and keep the format).

View 12 Replies View Related

Calling Non-existing Sheets

Oct 28, 2009

Currently I'm trying to set up a table that referneces sheets that don't yet exist. These sheets will be added by the user or by macro, either one. I want my table to automatically take these pages into account when they're created without having to rewrite my formulas. The problem is that when I make the call to a sheet that's not there, it opens a file open dialogue box and I don't want it to do that. Is there something I can do here about that?

I don't think it's really going to shed any light on the subject, but this is my call (from memory, not copy and paste)

IF(OR(ISERROR('P01'!N2),'P01'!N2 = ""),"",'P01'!N2)

Also, when I enter this formula and the sheet doesn't exist, it automatically reverts to a text field and I have to manually switch it back to general in which case it gives a #REF error (which is just fine).

Also, I'm writing it in Excel 2003, but it completely crashes Excel '97 if I try to open it there, how can I fix this?

View 6 Replies View Related

Calling Routine From A Class In Add-in?

Feb 4, 2014

I've got three workbooks that end users will be using for data entry. The VBA code for the three workbooks is identical.

I'm trying to encapsulate the code into an add-in so I only have one copy of the code that I have to debug/maintain.

Furthermore, I've also encapsulated the code in the add-in into a class. The class isn't really a "usual" object modelling some data; rather, it's a utility class processing Worksheet_Change and Worksheet_Activate events. Hopefully this isn't a really bad idea, rather than keeping the code in standard modules in the add-in.

The application is called "Midas". Here are some code excerpts:

Midas.xlam:

Standard Module:

[Code] ......

Class Module:

[Code] .....

And in the end user workbook:

[Code] .....

The Wbk_SheetChange event handler is triggering, but it's looking for the code in the end user workbook, instead of the class. Is there a way I can change

[Code] .....

to call the private routine "Cases_Changed" (where "Cases" is the sheetname) residing in the class instead of looking for it in the workbook?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved