Excel 2010 :: Determine And Eliminate Unassigned Macros
Jun 21, 2012
I'm using excel 2010 and have inherited an old workbook that has seen many version updates over the years. The "view macros" list displays approx 25 macros and I know that not all are currently being used or necessary.
The main tab contains macro control buttons. Any macro not assigned to one of these controls is not necessary (it's probably old and was just never removed).
How can I determine which macros are "unassigned" to a control or otherwise invalid?
I have 2 lists (excel 2010) that I need to compare (they are currently 2 files, but I can combine them into 1 file with 2 worksheets) if it would be better.
The first list is of "All Students" at our college. The second list is those students who live "ON campus". I need a list of those students who live OFF campus.
The common denominator headers in both lists is: A1 Last Name A2 First Name
I would like to keep the "All Students" list as my master as it contains all the data I need such as addresses.
Ideally, I would like to create a macro or lookup or whatever that will take everyone from the "ON" list and remove them from the "ALL" list, leaving me with the data I need.
Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
I'm haven't played with stats in a while and now I'm trying to determine the ideal traffic/labor hour in order to maximize revenue/labor hour. My data looks like this:
Excel 2010 A B C D E F
1 Date Traffic Revenue Hours Traffic/hr Revenue/hr
[Code] ........
Sheet1
I don't know if Excel or Analyst Toolpak has the firepower for this or if I'll need additional tools.
I need some different columns within a row to calculate based on a statement found in an earlier column in the row. something similar to below. there will only be two statements total which are noted on the table below.
A B C D E
1 0 0 20 5%
[Code].....
So basically If column A = disposition calculate D and E in column B but if Column A = Receipt calculate in column C
The formula for D and E is not exactly as shown just using that as a quick example. I'm using office 2010.
I have a macro to collate data from multiple workbooks to a single sheet. The folder path to the source files can be chosen thorugh the macro. It runs perfectly in my system running on Windows XP. But it doesnt run on Wondows 7 system. Both are using MS Excel 2010. In Windows 7 system, while choosing the folder path where source files are located, it says "No items match your search" (screenshot attached). I would want the macro modified so that it runs on any platform. The macros that I'm using is given below:
I have a 'dashboard' worksheet in 2010 and want to be able to click on a cell in this sheet and have it take me to the assigned worksheet. They are all in the same workbook. e.g if i click on Leadership i want it to take me to the Leadership tab, Skills to take me to the Skills tab etc The 'dashboard' will be the only viewable sheet in the workbook until one of the items on the page has been clicked.
I have a very basic understanding of macros and I'm not sure if this would work or if i should use a formula instead?
I developed a tool in Excel 2010 using macros and launched it to a group of dozens of users. Turns out one of them uses a mac and says it is incompatible. I have never used Excel for Mac or tested my tool in it. Next I plan to test it on a mac but how to make this work?
How/ where to save macros. If I save a macro "normally" ("in this workbook") then it only applies to that one file, right? And I can't use it in any new files.
So what do I do if I want a macro I can use in "all" my Excel files?
And can I email a file containing a macro to a colleague, so he can work on the file, using the macro too?
I ask, because I've had trouble with this in the past (tho in MS Word) where I couldn't get the macro to be emailed along with the file...
Also - how can I get an .xls file with a macro to work in newer versions of Excel? And can I use Excel 2010 to save a file with a macro in an .xls format, so it can be opened in older Excel versions too?
I have inherited support for a suite of Excel 2003 spreadsheets with complicated macros which run fine on XP. Having been tasked to test them on Windows 7 with Office 2010, I have not converted them as they are run by several sites globally who may not upgrade to Office 2010 at the same time. Hence they run in compatibility mode which in general is fine.
However, certain macros are veeeery slow and to the user would look like the app has hung. In debug I have found that the macro takes 10 minutes plus whenever it hits any of the following code:
Code: With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With
ActiveWorkBook.PrecisionAsDisplayed = False It goes slow on each of the three 'lines' so it seems that it is actually doing an auto calc each time!
Is there some configuration I can do to prevent this? Setting auto calc to Manual didn't work and anyway I loose things, like data validation, when I save it in Excel 2010.
I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.
However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...
How can I update each sheet individually, without compromising the other sheets?
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else Row 2 is my header, so cell A2 says "ID" Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I have a workbook with numerous macros in it and they are assigned to buttons in the different worksheets. I am trying to record a new macro and when I start recording and click on one of the other macros nothing happens. This wasn't a problem in excel 2003.
I currently have an excel workbook (2010) in which I created a custom ribbon and assigned several different macros to the buttons.
Long story short, I will be saving several variations of this workbook every so often when I get new data.
The problem is that if I change the name of the file (Save as or Rename), the custom ribbon buttons no longer work. When I assigned the macros to the ribbon buttons, the macros were absolutely assigned.
Is it possible to assign relative macros to custom ribbons?
From research I have done, it appears that custom ribbons cannot be created using VBA. Is this still true?
I've created a spreadsheet at work that has two summary tabs which contain hyperlinks to around 30 separate sheet tabs.
On each sheet tab there is a list of unique values in column A (and other information relating to each value in columns B to D which are repeated for more than one unique value). In column E, users enter a test script name against each unique value they wish to 'reserve', and the macro picks out the unique test script names and via the COUNTIF formula counts the frequency of each test script name for each of the different values in column B.
My problem is that the macro seems to work fine if the workbook is not shared, but errors if the workbook is saved as shared. The error is 'Run time error 1004 - Unable to select the MergeCells property of the Range class'.
Here is the macro code:
Sub Get_Policies_Per_Script(updCol As Long, ShtName As String) Dim rowctr As Long Dim tgtrow As Long
Const ppsformula As String = "=COUNTIFS($A$3:$A$65000,I$24,$E$3:$E$65000,$G"
If updCol = 5 Then 'test name column has been modified
There are some excel documents in a hidden folder that contain sensitive information. Shortcuts to these documents will be provided to users to open them and add data. These documents contain all kinds of macros as well. Once macro disables the 'Save As' feature so that the workbook can not be saved outside of the hidden folder, preventing to some degree a user from saving as a different name and emailing the document with the sensitive data.
This all works fine with Macros enabled. As soon as macros are disabled the 'Save As' prevention is bypassed and the workbook can be saved anywhere.
Is there a way for an administrator to lock out the Trust Center options for disabling all macros?
I have a 2003 Workbook that is around 7 mb. It used to be half of that but it jumped up in size which makes it harder to email, etc.
On the front page it would go out past the (known) data and I was able to reset it to the correct place but on the 2nd sheet it would go to IV 65536 without fail. I used VBA as well as physically deleting andor clearing rows and columns but to no avail.
I decided to start eliminating portions of the workbook to see if I could find where the size was coming from.
1. I deleted Regular formatting 2. I deleted Conditional formatting 3. Made everything the same font name and size, etc 4. I deleted all VBA Code. 5. Last but not least I deleted/cleared all data from all the sheets
Between each change I would save and close the workbook and check the size in Explorer. Each deletion brought down the size, however after finishing step 5, I had a blank workbook with 3 sheets and it was still over 1.4 meg!
The next step was to remove the 2nd sheet. I did this and it went from 1.4 meg to 25 kb.
At this point I have been coping all the info into notepad and the from notepad into a new workbook.
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
I am trying to come up with a formula to determine if someone is 21 years old or not. After I enter their DOB if they are 21 or over I would like the cell to turn Green, if not I would like it to turn red. I have tried alot of different formulas that primary pertain to days and months but nothing for years.
Is there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.
I'm trying to copy the content of a word document (File A) to another (File B) using Excel VBA. File A has about 100's of pages (not sure of the number as it varies) and this needs to be split to different files, each having 15 Pages.
Below is my code, where I'm able to select the content of the first Page and paste it in the target folder, but not sure how to determine the number of pages in word using excel VBA.
Note: Copying the content should be done, page wise only.
If I have 1,000 entries in a column is there an easy way to determine the number of times a certain item is in the columns. For example if field A1 is "qwerty" I wan to see how many times "qwerty" or field A1 appears in the entire column?
Is there a Color Function type vba code that can give me the color integer? I have one that can do that for cells that I've manually colored but I can't find a code that works on conditionally formatted cells.
Trying to manipulate a macro I recorded to be slightly more dynamic with some VBA code. The macro has saved me a lot of time however each month I have to re-do it due to the date changing and I'm sure there must be an easy way to adapt the below code.
Current Code (it repeats afterwards as there are lots and lots of sheets I need to perform the action on).
What I'm after is a way to alter the range highlighted based upon matching a cell in "Raw Data.xls' and a cell in "Volume Tracking.xlsm".
The cell A43 in "Raw Data.xls" contains a month (which changes every month); if the month is Jan then I would want the Range to remain as AE33. If it's Feb then I would want the Range to be AF33 (and so on).
In "Volume Tracking.xlsm" I have the months in cells AE4 (Jan), AE5 (Feb) etc.
Is there a way to locate the column in "Volume Tracking.xlsm" on row 4 that contains the value found in A43 in "Raw Data.xls" and then paste what was copied out of "Raw Data.xls" C43 and paste it in to row 33 of the correct column that holds the matching month?
If not could I add code to say if cell A43 in "Raw Data.xls" = Jan then paste to AE33, if Feb then paste to AF33 and so on.........
I am currently volunteering for a small organisation. I have spent the last week designing a userform to be used by other staff members in their office. However, when I asked the manager 'Do you have Microsoft Office?', I got a very firm and definite 'yes' in response.
It turns out they are using LibreOffice, which is a free alternative. I have also tried to get my userform to work with Open Office - but cannot seem to get either to work.
I have changed the settings in both suites of software to 'Load/Save Basic Code' and enabled macros to run via the security options.
My userform is not that complex. I have limited knowledge of VBA and zero experience using Basic, so have come up stuck.
I currently use an Excel program (with macros) that run weather forecast data. It is compatible with versions XP, 2003, 2007 and 2010. However, I am trying to figure out if I can run it on a Mac. My plan is to run windows on my Mac through Parellels or VMWare Fusion.
Here is a link to the Excel Program: TX Tornado Forecast Worksheet
I have a number of sheets that have sheet macros that are identical.... i am changing the sheet macro in 1 and want to see if there is an automatic way to distribute it to the other sheets automatically.
