I have two short macros, shown below. The first changes the font color of the cells in named range BackgroundFont1 to the background color of the cells. The second macro turns the font color in these cells back to black.
I call these subs in other macros. The first one works all the time. The second one works only occasionally - more often than not it just hangs up Excel entirely and shuts down the program. It happens even if I just run the BlackFont macro on its own.
The error message I get at times is "Method color of Object Font failed".
Macro 1
Code:
Sub BackgroundFont1()
For Each cell In Range("BackgroundFont1")
cell.Font.Color = cell.Interior.Color
Next cell
End Sub
Macro 2
Code:
Sub BlackFont()
For Each cell In Range("BackgroundFont1")
cell.Font.Color = vbBlack
Next cell
End Sub
These are the cells in the ranged named BackgroundFont1.
I need to run a very simple macro (filter by date, copy, and paste) on every worksheet in a workbook, and then place that data into a summary page at the "end" of the workbook. Most other posts had to do with some kind of formatting that would work in succession on the sheets linearly from left to right (for lack of a better way to put it). My problem, however, is that the data would need to be pasted into the summary worksheet, and then somehow the macro would come back to the next sheet (after the one it just did) and do it all over again, so on and so forth. The worksheets are arranged alphabetically, but other than that there is no sort of easily identifiable progression between them (i.e. Sheet1, Sheet 2, etc.).
I have a rather large workbook (30 sheets, 10MB) that has one worksheet with many INDIRECT functions in it (pulling data from the same file, different tabs). I am working to put simple code into the workbook to protect and/or unprotect all worksheets. I have gotten code to work to both protect and unprotect all the sheets, but when I run the unprotect code (see below), and then I go to edit the workbook, data from the sheet with many "indirect" functions temporarily "overwrites" the data on the active sheet (this is fixed when I scroll my mouse over the effected cells). I am developing this workbook for other users, so I'd like to fix this before sending it off to them.
This problem does not happen until after I run the following .....
I have a problem in getting VLOOKUP to evaluate consistently. It is best understood from the screenshot linked (Office 2010), where cells B3 and B4 are not showing the expected values. I wanted to upload the excel file, but cannot see the option to do it.
The screenshot is at Book2 | Flickr - Photo Sharing!
This is just a small segment of the big excel sheet.
I'm trying to write a basic macro which will increase the value in a cell by 1 when I click on the button I've made. I've searched for hours on the Internet, including this site, and found several websites which give a formula needed but none of them work, I always get an "Object variable not set" error or something about sub-functions which I can't recreate so I can't give the full error message. Sorry.
Based on what I've seen on the Internet, I currently have:
I have a really large excel file (90 Megs) And i want to copy the values and formats of certain sheets to another workbook. Ive tried using a copy/paste macro i had from work but the sheets have alot of groups and info so it kept freezing
I've recorded a macro which selects a few cells in a row, copies them and pastes them again over the same cells (special paste - only values), and I've attached this macro to a button.
I would like the macro to repeat the same action on the next row for the same columns, each time the button clicked.
How to update the macro to move to the next row each time initiated?
I am creating a new form, and have 2 option boxes. I have linked these checkboxes to a cell which gives a calculation depending on what is selected (cell E10). If the value of this cell is 2, then the person has selected that they don't need to fill in the details below. If this is the case then I would like to hid the entire rows below (rows 18 - 45 to be precise).
At the moment I have used data validation to blank out these rows, but I'd much rather group them automatically to avoid a large gap between the option boxes and the next section.
I am trying to create a simple macro to open a second workbook if the value in a cell in the current workbook is 'x' or if 'y' do nothing.
Basically I have a simple sales ledger, one column has a yes/no for whether or not an invoice is paid if no I want to open a second workbook 'debtors' so that I can manually input the details. I'm sure this is a very simple proceedure but I can't seem to crack it.
I am using excel 2007 for database of a large e-commerce site. I am creating search-able attributes from keywords found in description, title and meta-data columns.
I am using the following function in "column X" to search 4 columns (see formula) for specific music styles.
I am exporting data from a database and will want to split up the information into separate worksheets & eventually into separate workbooks.
What I am looking for is a macro code that will automate the cut/paste of information onto other worksheets. I know I can record the macro but the problem is the data sets vary in size each quarter.
What I need is a macro code that looks for a particular phrase and when found it automatically puts all rows undernear (until phrase appears again)on a new worksheet....make sense? The phrase might appear twice or 50 times, so each time it is found the data following will cut/paste.
I am trying to implement a simple macro. Here is my Function Age(DOB) Age = Int((Now - DOB) / 365) End Function
Sub Macro1() Age (Selection) ActiveCell.Offset(0, 1).Select ****** End Sub
The function basically calculates the persons age using their date of birth. What I need to know is how to copy the result of the function into the cell next to the selected one. (The code above only selects the adjacent cell).
I'm only starting to get into the Macro side of excel, and I've created a couple of macros to automatically format cell contents and the like. However I'm having trouble trying to divide some cell contents by 100.
I have 2 files I'm working with, one contains information regarding cd's and percentages, however the percentages in this file cannot be formatted to percentages (so the powers that be say) for whatever reason. I copy all this information into another file which does have the percentages formatted as percentages, the result is that the values get multiplied by 100.
So values that read: 100 50 50.25
for example, appear on the new sheet as: 10000% 50000% 5025%
Is it possible to write a macro that will divide these percentages by 100 so the read correctly as: 100% 50% 50.25%
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.
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.
I'm trying to get a macro to run when the user presses the save button. I have the following code:
[Code] .......
This code is supposed to apply the TRIM function to every worksheet in the workbook when the user presses save, and the saves the results. I understand the Private Sub bit is the code to have the macro run during the save, but for some reason the macro itself isn't actually running. I'm not getting any error message, its just that the cells which should have the TRIM function applied to them aren't being corrected.
I have a macro that takes about a minute to run. I would like to add a progress bar to appear once the macro starts running. There are no loops in the macro. What are my options?
I have 3 different sheets with a private sub on each all labelled
Private Sub CommandButton1_Click()
The macro's runs fine on each page.
I want to put a macro on a separate sheet that i can run each of those macro's
I did initially copy the original private macro and change the
Private Sub CommandButton1_Click()
to
sub report()
But I couldn't do all 3 in the same manner. i did change each name to something different... the other 2 subs did run, but they didn't do anything except put the massage box on the end saying that "the macro has finished".
I have 7 different workbooks as part of my daily routine.
At present the first workbook is opened via the task scheduler, and the macro is run using the following code
[Code] .....
This then opens the next workbook and closes the current one. The next workbook then runs its macro again from a timed call
[Code] ...........
This repeats for 7 workbooks. The problem is, if a macro overruns - the whole schedule is out of sync when macro's are called depending on the time. What I would like to do, is link each macro by calling for the next one, and closing the current workbook. However, when i have tried to do this, the macro has always stopped when the workbook has been closed - the next macro doesn't run.
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.
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.
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.
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?
