Combine 2 Working Macros Into One?
Aug 3, 2014I now have 2 working macros but cant seem to combine them correctly. How do I get them both into one macro so they run together.
View 2 RepliesI now have 2 working macros but cant seem to combine them correctly. How do I get them both into one macro so they run together.
View 2 RepliesI have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
I probably have a odd request, but then I think I'm just different ?
I have an Excel Workbook with Col. A approximately 1/8" wide. The formatting of this col is that the cells are set to have the typing fit into the cell, therefore it becomes so small that no one can read it. Just what I wanted. If the user needs to read what's in the cell, they can click on the cell and read it in the formula bar.
Col. B is approximately 2" wide
1st, the user enters the Path and File name, with extension, into cell A34(example).
2nd, the user types in a File Name that they will best understand into cell B34.
The Workbook is now saved.
When the user wants to open that file, they click on the Name . . . Col B34 - Easily recognized by the user.
They then click either the Button that say's "Open Excel File" -or- "Open Word File.
The macro automatically moves the selection over to column A, then the file is opened.
Now what I need
I want to use just 1 button and I will name it "OPEN"
The 2 below macros work excellant and I still need the functionality of each. What I want is that 1st it trys to see if it is an excel file and if so, opens the file and stops.
If the macro determines that it is not an Excel file, the macro then goes to the Word macro and determines if it really is a Word file and if so, opens the file and stops.
NOTE: I want to point out that I still need all the checking etc that the 2 macros currently do.
Here are the macros
OPEN EXCEL FILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
I have 2 macros that work as I want them to. First one gets ride of formulas and formatting etc. and then deletes itself.
Sub Quote_Wrapup()
Range("A18:A1018").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E").Select
Range("A980").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1021").Select
ActiveSheet.Shapes("Picture 14").Select
Application.CutCopyMode = False..................
I don't really understand how to combine them so that they will work as one.
I've got two macros that work beautifully apart from one another ... but put the two in the same Excel workbook and they bump heads. I get the infamous "Ambiguous name detected: Worksheet_Change"
I know enough about VB to get myself into trouble. So, I'm asking for help from a talented person to show me how to combine the two ... or another work around to get through this.
Here are the two sets of code in my sheet that I think are causing the ambiguous name problem. How can I work around this error?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim varFind As Variant
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column 2 And Target.Column 9 Then Exit Sub
If IsEmpty(Target) Then Target.Interior.ColorIndex = 0
If Target.Column = 2 Then
Set varFind = Columns(16).Find(What:=Target.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
If varFind Is Nothing Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = Cells(varFind.Row, 16).Interior.ColorIndex
End If
End If
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?
I have a spreadsheet I use to track reprimands for our employees. Reprimand Counseling Spreadsheet Example.xlsm. Previously, I had to read the entire reprimand in order to determine the policy violation(s) and then manually enter those reasons into the cell. We have a new standardized form, where the manger selects the reason(s) for the reprimand. I wanted a drop down box that I could select multiple reasons for the reprimand and have all the reasons appear on the same line, as well as being able to remove a reason from the line by selecting it again (so I wouldn't have to start over, in case I selected the wrong reason). I was able to find this code, on [URL] .... to accomplish that.
Same Cell Add & Remove
I also need to be able to manual edit some of the reasons. For example "Missed Meal Period(s) #___". I will need to be able to edit that item to read "Missed Meal Period(s) #2". I was able to find this code, on the same website as the previous code, to accomplish that. However, this code includes a line break, which I do not want.
Edit Same Cell
[Code] .....
Since I need the code to work on each worksheet (CIVI - SOCR), will I have to enter the code on each worksheet or is there a better way to accomplish this? Is there a way to combine these two codes, while simultaneously removing the line break? I need to select multiple reprimand reasons in the same cell and edit the reasons, if necessary. I've read and reread that article for hours, but I can't get it to work.
I have two pieces of code. The first one puts a tick mark in the cell when you double click it. The second one hides column B of another sheet if the value in cell A1 of Sheet1 is "a". I want to combine these two codes so that when I double click cell A1 of sheet1 it puts a tick mark in the cell and hides column B of Sheet2 and clears the contents of range B2:B50 of Sheet2.
View 4 Replies View Relatedcombine two Worksheet Change event macros into the one macro? The macros are listed below.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
[Code]...
exitHandler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O:O")) Is Nothing Then
Application.EnableEvents = False
[Code]...
I have a workbook which adds command bar named "Upload Processing" to Excel application if you open it.
The weirdest thing is, it hasn't got any macro stored anywhere or at least I can't see it anywhere (Alt+F11).
Note: Don't forget to use application.commandbars("Upload Processing").delete command from VBEditor to get rid of this newly added command bar when you are trialing.
I'm trying to do is delete a row of chosen cells in my form (form is locked with a password) using a macro then return back to the first cell below the deleted row. When I run the macro what's happening is that it's returning back to the cell chosen while writing the macro (which is at the very top A7). So if I'm several rows below A7, it deletes the row and then returns all the way back to the top of the form causing the user to have to scroll back down to where they were
View 3 Replies View RelatedAll my macros have suddenly started crashing Excel 2007 when I try to run any one of them. I can record a new macro but even that newly recorded macro will crash Excel.
Service Pack 1 was downloaded and installed by Windows Update two days ago and I had successfully run macros after SP1 was installed.
Today, it's a no go and I'm at a loss as to what may have caused this problem. I have not installed any software lately, other than the Windows updates on patch Tuesday.
This is on a Vista Home Premium machine.
When I try to run a macro, Excel crashes with this info:
why my Macros have suddenly stopped working for no apparent reason?
For Example - one macro would run when a Cell in Column A was selcted. It would trigger a macro to save the workbook.
I'm not giving details of what the specific macros do as it's more of a question as to why macros in a workbook just suddenly cease to function.
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:
[Code] .....
Untitled.png‎
I am trying to password protect some of the cells in a worksheet. However doing that starts giving errors with Macros. The Macros are no longer working and infact giving error. What can I do to stop that happening?
View 9 Replies View RelatedI recently had a virus on my computer and had to replace it. I had a ton of macros saved in my Personal.xlsb workbook, so I exported those modules and have now imported them to the new Personal.xlsb file. Now for some reason those macros no longer work in other files. Most of them are basic, like I have a macro to paste values or paste formats, etc. but I have some others that are very complex. The macros work fine as long as I am in Personal.xlsb, but if I try to use them in another open workbook I get the "all macros may be disabled or the macro may not be available in this workbook" message. I am using Excel 07 and have verified that macros are enabled.
View 3 Replies View RelatedI have created a workbook ( with protected sheets) containing two macros " To Insert a Row ( Copying the formula from the row above) and "Delete a Row", both, assigned to a customized toolbar.
when I move the workbook, say from the folder where I created it originally to the desktop, the macros stop working giving error like THE MACRO " C:so n so... CAN NOT BE FOUND!
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
What is the commands or script for deleting a macro automatically using
another workbook macro.
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies View RelatedI have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
I have 12 workbooks (one for each month), they have a list of assets to our company and a running total of all expenses for the month that they incurred. The 13th workbook is a yearly analysis of each asset. I need to transfer all charges into this worksheet.
So for example:
Worksheet 1 (January)
Column A Column H
Asset # Total Expense
5021 $3,041.52
970289 $242.08
Worksheet 13
Column A Column B Column C Column D
Asset # Asset Description January February
5021 Rivet Machine $3041.52 $345.65
So hundreds more machines for twelve months can take awhile to populate this info and very confusing when dealing with separate worksheets.
I need to get 5 small subs into 1 sub. (run the whole program by pressing once.
Please see attached file with codes : ServiceDriftMaaling.rar
Ok, These are the three formula's. How do I combine them or can I.
=IF((B2="GOV"),[@[Gal Billed]]/1000*1.5)
=IF((B2="NPROFIT"),[@[Gal Billed]]/1000*2,0)
=IF((B2="COM"),[@[Gal Billed]]/1000*3)
I need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.
View 6 Replies View RelatedI have two formulas that i need to combine as one, but i dont now how.
This is the other formula:
=SUMPRODUCT((Huollot!I2:I500="Ty”n alla")*(Huollot!K2:K500="Warranty")*1)
The other one:
=IF(NETWORKDAYS(Sheet1!C1;Huollot!C2:C500)>20;1;0)
the biggest problem is that this formula does not work as it is. Or is there some other function than networkdays that i could use?
The purpose of the formula is to count how many "objects" that are in service have been there for over 20 days. There a a few other criteria also, but sumproduct takes care of that. Can networkdays even be used the way i´m trying to?
In cell B1 I have the number 1
In cell C1 I have the number 1031000
I know how to combine the cells in A1. I am getting a result of 1-1031000
What I would like to happen is that 1 becomes 001.
Ending result is 001-1031000
problem is that cells in B could be one, two or three digits.
On a small scale I could fix manually but i have over 15,000 rows to deal with