Macro That Creates Many Macros
Jan 17, 2009
I am having a problem trying to create a macro that will loop in order to create several macros. I have a long macro (call it mainmacro) that needs to be able to create several simple macros to be used by the user of the workbook. Each macro has a variable that needs to be taken from mainmacro. Each macro is different based on the value of the 'i' loop variable that the macro is created in. i'm sure im making this sound more complicated than it really is...basically:
Sub mainmacro()
'lots of code not relavent to this issue here
For i = 1 to 10
'create 10 macros that, when run, would highlight A1, A2, etc.
Range("A" & i).Select
Next i
End Sub
This is just an example. But, basically, i just want to be able to automatically create a bunch of macros with a variable from mainmacro.!
View 11 Replies
ADVERTISEMENT
Dec 15, 2009
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.
I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)
View 3 Replies
View Related
Jun 20, 2006
I am trying to use a macro that creates sheets and places formulae in cells but the resulting formulae are not what I coded. Here is similar
Function CreateSheet(i As Integer)
Dim CellAddress As String
Set Adjustment = Worksheets("Sheet1").Cells(6, 3 + 4 * i)
Celladdress = Adjustment.Address(ReferenceStyle:=xlR1C1)
With Selection
.Offset(22, 9).Formula = "=Sheet2!B14*Rating!" & CellAddress
.Offset(23, 9).Formula = "=Sheet2!C14*K4*Rating!" & CellAddress
.Offset(24, 9).Formula = "=Sheet2!D14*K5*Rating!" & CellAddress
End With.............
View 3 Replies
View Related
Jun 1, 2009
I need to add to a current macro which currently copies the master tab and creates a new tab with tomorrow's date on it. For reference here is the current macro:
View 3 Replies
View Related
May 27, 2014
Macro to copy data from Excel sheet and creates a Pipe delimited text file.
View 10 Replies
View Related
Mar 13, 2007
I have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:
numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)
Next
Private Function calculateSomething(counter As Integer) As Integer
Dim strCounter As String
strCounter = counter
With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
Next
Next
End With
End Function
The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.
View 5 Replies
View Related
Jul 16, 2007
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies
View Related
Jan 28, 2009
I added a new sub to and now I'm getting a compile error. This was working fine until I added "Cust_Rev1". I get an compile error saying "expected variable or procedure, not module". Both are located in personal.xlsb. If I rem Cust_Rev1 out, I don't get a hiccup. (FWIW, I've been running "Cust_Rev1" on its own to debug it.)
View 2 Replies
View Related
Feb 15, 2007
I have a list on my first worksheet that is sequentially numbered in the first column, and has work activities in the next column. On my second worksheet, I have those sequential activity numbers as the column headers on a new list. Problem is I don't have any room to label the new list by the activity's actual description(second column, first sheet). If I did, I would just use a simple VLOOKUP.
So, what I would like to do is utilize the VLOOKUP to pass the activity description string to a UDF that will create a comment in those column headers. Then when the user mouses over the activity numbers, the respective activity descripiton will pop-up as a comment thus solving my space problem!
View 8 Replies
View Related
Apr 2, 2007
i have the formula =CHAR(INT(RAND()*25)+65) which creates random number when dragged from say C1 TO C6 however i need the 3rd 4th or 5th character to be a random number between one and 9 is this possible?
View 9 Replies
View Related
Jul 30, 2007
i have a combo box that contain range of number from (1-9)
i need some help in creating the yellow boxes automatically after i have choose the number that i want from the combo box
View 9 Replies
View Related
May 23, 2009
In case i creates a list using validation in column d ( for ex list contains either A or B or C ), now i want to create another lis in column e but wants that this new list will relate to list of colum D , i.e. in case some one selects A from drop sown list of column D then one list appear containing some options to select or if he selects b from drop down list in column D then diff list appear in column e ,
View 9 Replies
View Related
Oct 14, 2009
I have 5 macros that update/gather info from the net, and instead of running each of these individually, i would like to be able to create 1 super-macro that runs them all. How would i go about doing this?.. say they're called M1, M2.. M5..
View 2 Replies
View Related
Feb 21, 2013
I have several commonly used macros saved in my personal.xlsm file so I can tap into the power of these macros on all spreadsheets without having to re-create the macros in every worksheet I do.
The problem I have is that it's slow to recall these macros by clicking on the Developer-->Macros button. I'd much rather assign a hotkey to each...but the Hotkeys only work with ctrl+letter and I find that I accidentally assign my macros to hotkeys that are already hotkeys (example: ctrl+z for undo).
How can I create a macro that gives me a pop-up box with buttons I can push to activate the macro I need and then the pop-up box goes away after selecting the macro (something like a message box pop-up)?
View 2 Replies
View Related
Jan 5, 2013
I have a database (ATTACHED) which contain name, roll number, courses taken etc. I want to create different file for different courses showing the roll number and name of the student who have written their name as REGISTER in the course.
View 3 Replies
View Related
Dec 28, 2007
I have a vbscript that creates a workbook in an existing spreadsheet. I would like to have the script create the spreadsheet if it does not exist. That part is easy enough. The part I don't know how to do is to have the macro that has to run on the sheet created by the script.
View 9 Replies
View Related
Dec 12, 2006
I've written some code that fires on a worksheet_change event. It worked fine for the initial bit of code I wrote, but then once I repeated it for several different ranges, I got an error stating COMPILE ERROR: PROCDEURE TOO LARGE. (This amount of code below works fine, it's only when I add the rest it becomes too large.)
Private Sub Worksheet_Change(ByVal Target As Range)
'Unprotect Sheet
ActiveSheet.Unprotect Password:="clemson"
'Freeze screen
Application.ScreenUpdating = False
'Turn Off Auto Calc
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
' Master Bath Shower Listello
If Not Intersect(Target, Range("Listello1ShowerMasterBath")) Is Nothing Then
If UCase(Target.Value) = "NONE" Then
Range("Listello_1_Options_Shower_Master_Bath").EntireRow.Hidden = True:
Range("Listello_2_Shower_Master_Bath").EntireRow.Hidden = True:..........
View 9 Replies
View Related
Jan 12, 2009
I need a macro that will create a popup message any time 1) a new worksheet is created and 2) a worksheet is copied. The contents of the message left aside (use "message" as our example message)
View 9 Replies
View Related
Mar 19, 2007
i need to create an user form that creates an ordered list when an input for the starting number, the ending number, and the increment is entered by the user. the ordered list must also start in the specified cell that is entered by the user. i understand how to design the user form but the coding necessary for making the macro baffles me.
View 4 Replies
View Related
Jul 11, 2007
When ever I add a secondary axis to my chart my bar chart sub type changes to a stacked bar chart. How do I keep the sub type bar chart? I have attached an example. I have tried playing with the gap and width of the bars but to no avail.
View 3 Replies
View Related
Jan 28, 2010
Is there any way to nest a macro within another macro?
I have a macro inside of a module, and when the user clicks on a button to invoke that macro, it does some calculations, and then I need it to call another macro within the same module to do some calculations on another sheet.
View 2 Replies
View Related
Apr 23, 2014
So I have a macro that sends an email of the active spread sheet. I want to add that macro to another macro. The tricky part is that i call other macros inside the macro. So is there a way i can add the send email macro and have it apply to all the macros i call. Here my code:
Code:
Sub Run_All_Billing()
'
' Run_All_Phones Macro
' Make sure you open "gateway.csv" "phone.csv" "ctiroutepoint.csv" and "Billing Contact.xls"
' Call Gateway Workbook and clean it up
Windows("gateway.csv").Activate
Call Clean_Up_Gateway
' Call Phone Workbook and clean it up
[Code] .........
Here's my send email Macro
Code:
Sub Send_Email()
'
' Send_Email Macro
'
Dim oApp, oMail As Object
Dim WB As Workbook
Dim ArchiveFolder As String, FileName As String
[Code] ........
So I want to add that send email macro into my Run all billing macro and it apply to all the macros I call.
View 2 Replies
View Related
Jul 24, 2007
I need to write a macros that will activate another macros whenever the cell "I4" is modified. So far it is not working the way it suppose to. Maybe anyone can spot a mistake? Here are both Macros that I have: ....
View 9 Replies
View Related
Oct 23, 2006
I am trying to use the EnableMacros. zip script located here {url} But I am having a hard time making it work!
1. I have copied the module in EnableMacros.zip and imported it into my workbook.
2. I have placed identical code in my workbook's ThisWorkbook as is in the EnableMacros.zip's ThisWorkbook.
3. I have copied Sheet1 from EnableMacros.zip titled "Info Sheet". In MY workbook it is sheet7, but it is titled the "Info Sheet" in the EnableMacros.zip.
4. I have also copied sheet6 from EnableMacros.zip into my workbook and titled it "LoadScreen". In EnableMacros.zip the sheet is titled "Sheet1"
...
View 4 Replies
View Related
Oct 12, 2007
I have a Pivot table.
Layout:
Rows: City, Restaurant, Cashflow
Column: Date (Month/Year)
DataField: $Amount
"City" might be 7 cities
"Restaurant" may be 32 restaurants, some in some cities; but not other
"CashFlow" can be either "Revenue" or "Expense"
The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist.
Now.... the trouble...
I added a Calculated Item, "Profit" which = "Revenue" - "Expenses".
Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "$0.00" since there are no "Revenue" or "Expense" entries.
How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination?
Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?
View 11 Replies
View Related
Jun 17, 2009
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.
View 2 Replies
View Related
Oct 4, 2007
I'm am running Excel 2003 SP2 on Windows XP SP2.
I have a macro which modifies cells in a spreadsheet. But for brevity sake, I have an empty spreadsheet which has an Auto_open macro and two subroutines in it. The subroutines initially consist of just the Sub and End Sub statements.
The Auto_open procedure reads in lines from two text files (generated by another application), and inserts the lines into each of the empty subroutines. Auto_open then runs the two subroutines.
I have been able to get this dynamic creation of the subroutines to work for one subroutine, but not for the second. I receive the compile error: "Only comments may appear after End Sub, End Function, or End Property". I don't see anything wrong with the inserted code.
The modified subroutines appear fine, and if I save the macros with the modified code, close excel, rename the text files so they are no longer read in, and re-open the spreadsheet, the auto_open procedure and the two subroutines run fine. This tells me the code itself is okay, and yet it won't work during the initial run.
Below is the macro code in its original state:
Sub Auto_open()
Dim fso, f
Dim VBCodeMod As Object
Dim LineNum As Long
Dim StrFileName As String
ShowVisualBasicEditor = True
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
End Sub
----------------------
The contents of the read in file, %TEMP%subA.txt, is just one line:
MsgBox("inside SubA")
The contents of the read in file, %TEMP%subB.txt, is just one line:
MsgBox("inside SubB")
------------------------
When I open the spreadsheet the first time, it gives me the first message box from Sub A, but then generates the error and highlights the Sub SubB() line.
To duplicate the problem:
1. Insert the macro into a spreadsheet. Save and exit it.
2. Create the files %TEMP%subA.txt and %TEMP%subB.txt containing the single MsgBox lines.
3. Open up the spreadsheet. SubA will run and a message box will appear. Then the compilation error will occur.
4. Save and exit the spreadsheet.
SubA will now contain:
Sub SubA()
MsgBox("inside SubA")
End Sub
SubB will now contain:
Sub SubB()
MsgBox("inside SubA")
End Sub
5. Rename the two text files, so that the next time you open the spreadsheet it won't try to insert the lines from the files into the subroutines.
6. Re-open the spreadsheet. Two message boxes will now appear, one from SubA and one from SubB.
View 9 Replies
View Related
Nov 28, 2012
I have 5 macros to be run in sequence. I have copied them into the file attached with ----- line as separator between them.
How can I possibly combine them all into ONE MACRO?
View 5 Replies
View Related
Apr 3, 2009
Everytime I open the excel file I am working on I get a message saying that the file contains macros.
I have deleted all the macros I could find under "Tools" and "Macros" but I still get Module1.sortie, sortie and sortie listed. I can delete Module1.sortie but for the other two, sortie and sortie, I only have the "cancel" and "create" options.
View 14 Replies
View Related
Feb 2, 2012
I have several macros that import several files.
I'd like to create one macro that runs all the other macros. The sample below works by running Import_01 and Import_02
Code:
Application.Run "'FY13 Budget Worksheet - 400 Student Affairs - BACKUP.xlsm'!Import_01"
Application.Run "'FY13 Budget Worksheet - 400 Student Affairs - BACKUP.xlsm'!Import_02"
The problem is this filename may change. I'd like to remove the reference to 'FY13 Budget Worksheet - 400 Student Affairs - BACKUP.xlsm' so that the above code will work should the user change the filename.
View 2 Replies
View Related