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.!
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?)
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:
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.
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???
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.)
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!
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?
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 ,
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..
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)?
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.
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.
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:..........
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)
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.
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.
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.
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
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: ....
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" ...
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?
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.
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.
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.
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.
