New to the site and Macros - Combining macros in to one

bvbull200

New Member
Joined
Jul 29, 2013
Messages
23
Hello all. I have used this site extensively for a number of questions that I've had, but only now registered. Smart folks on this board, for sure.

By altering some macros that I found through searching, I have managed to get two macros that perform precisely the way that I want them to perform. I have to use both of them to get my desired result, though. I have created a macro that basically runs the other two macros in sequence, but it isn't quite what I'm looking for. The reason is, in order to modify this macro to work with slight variations (to the area labeled "Vendor Code"), I have to modify both the first macro and the macro that runs both (hope this makes sense), as I would like to have a Macro that performs all of this for multiple different Vendor Codes, independent of others. It would be easier if 1 macro just performed all of the functions and I could modify (or mirror) it to run a different Vendor Code. How do I put all of the functions from the two supplied macros in to one macro?

First I have it highlight all rows that have certain values in column E:

Code:
Sub HighlightVendorXYZ()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "VendorCode1" Or (cell.Value) = "VendorCode2" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
With del.EntireRow.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
        .PatternTintAndShade = 0
    End With
End Sub
Then I have it filter column E to show only cells that contain no fill. From there, it deletes all of those rows and goes back to the filter to display everything that is remaining:

Code:
Sub FilterNoFills()'
' FilterNoFills Macro
'


'
    ActiveSheet.Range("$A$1:$CN$285999").AutoFilter Field:=5, Operator:= _
        xlFilterNoFill
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$CN$7766").AutoFilter Field:=5
End Sub
Thanks in advance for any help.
 
Last edited:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DeusXv

Well-known Member
Joined
Jul 15, 2013
Messages
618
What you could do, depending on the number of different vendor codes there are is make an Add-In that has a drop down menu so you can select a specific heading for the type of vendor code you have.

You can make a menu using this code

Code:
Sub AddMenu()
'creates menu title
Set ExsitingMenu = CommandBars(1).Controls
Set Newmenu = ExsitingMenu.Add(Type:=msoControlPopup, before:=ExsitingMenu.Count, Temporary:=True)
Newmenu.Caption = "New Macro"
Newmenu.Visible = True
'
Set Newitem = Newmenu.Controls.Add(Type:=msoControlButton, Temporary:=True)
    With Newitem
    .Caption = "Title Name" 'name of item
    .OnAction = "Link Macro" ' marco name to be run
    End With


End Sub
From here then you can add more headings by just repeating this line of code

Code:
Newmenu.Caption = "New Macro"
Newmenu.Visible = True
'
Set Newitem = Newmenu.Controls.Add(Type:=msoControlButton, Temporary:=True)
    With Newitem
    .Caption = "Title Name" 'name of item
    .OnAction = "Link Macro" ' marco name to be run
    End With
and then all you need to do is link .OnAction to the different macro's so for your macro it would be

Code:
.OnAction = "HighLightMacroXYZ" ' marco name to be run
This should do the trick and also make it easier for any other users that need to use it
 

bvbull200

New Member
Joined
Jul 29, 2013
Messages
23
Thanks, DeusXv. There are some instances where that will certainly come in handy. It isn't a complete solution for me, though, as there are over 300 vendor codes and in many instances, more than one needs to be used on the same macro (multiple codes refer to the same single vendor). The other thing that makes this tricky is that the macro will not be a part of the spreadsheet as it is a report that is generated every day by creating a brand new Excel file. Given the size of the file and how long it takes to open already (280k+ rows, 50+ columns), I won't be able to have this macro added to the file since it will be utilized by only a small fraction of the people that access the file.

I plan on helping a small group of people load one macro on their computer that performs the exact functions of the two macros that I listed and just modify "VendorNumber" to suit their needs (each user only needs this for between 10 and 30 vendor codes).

Basically, is it possible to have a code that reads something like this:


Code:
Sub HighlightVendorXYZ()
Dim rng As Range, cell As Range, del As Range 
Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "VendorCode1" Or (cell.Value) = "VendorCode2" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
With del.EntireRow.Interior        
.Pattern = xlSolid        
.PatternColorIndex = xlAutomatic        
.ThemeColor = xlThemeColorDark1        
.TintAndShade = -4.99893185216834E-02        
.PatternTintAndShade = 0    
End With
ActiveSheet.Range("$A$1:$CN$285999").AutoFilter Field:=5, Operator:= _
        xlFilterNoFill    
Rows("2:2").Select    
Range(Selection, Selection.End(xlDown)).Select    
Selection.Delete Shift:=xlUp    
ActiveSheet.Range("$A$1:$CN$7766").AutoFilter Field:=5
End Sub
Where it rolls from one set of actions in to the other set in the same macro?

I plan on playing with the macro you listed later today, though. Thanks for the help.
 

DeusXv

Well-known Member
Joined
Jul 15, 2013
Messages
618
Well if you save the macro as an Add-in and have it running through the add-in menu then all you need to do is add that .xlam file to the Add-in folders on everyones comp and work and they will have that add-in, so it wont be related to just one excel sheet, so like it will appear like this in everyones comp



And you could try using a userform where the user enters in the vendor code and then link the code you have to each text box depending on what they type in. If you create a user form and double click a text box on it once you have it drawn it will open up a new VBA box for you to enter code linked to that specific text box.

And earlier on you were asking about running multiple macros in one go, for this all you need to do is make a separate module and inside it use these lines of code to call the other macros you have made

Code:
Sub NewModule

Call Macro1
Call Macro2

End Sub
This will save you having to copy and paste the code into each of the Subs for each text box
 

bvbull200

New Member
Joined
Jul 29, 2013
Messages
23
First of all, I sincerely appreciate your willingness to help. Somewhere down the line, possibly having the ability to type in the codes you want and have this run is extremely promising. I'll be referring to your submissions for guidance.

I think I am (admittedly) doing a poor job explaining what I mean. I don't want one macro that runs two other macros. I'd like for the one macro to be written in a way that it performs all of the functions that I had to write two macros to accomplish.

Currently, I need to be able to access different vendors at different times, with each vendor having their own macro. I currently did this for four different vendors. What I had to do was:

Take my first macro and change "VendorCode1" and "VendorCode2" to match the codes that correspond to that vendor. I then save this macro as "HighlightVendorXYZ". Then I modify my macro that runs both of the other two macros. It then looks like this:


Code:
Sub KeepVendorXYZ

Call HighlightVendorXYZ
Call FilterNoFills

End Sub
Then, to have one for Vendor ABC, I have to rewrite two of the macros and save them as something different. The first alters the "VendorCode1" spot to match the new vendor I am looking for. I save that new macro as HighlightVendorABC. I then have to add another macro that runs a different first macro and it looks like:


Code:
Sub KeepVendorABC

Call HighlightVendorABC
Call FilterNoFills

End Sub
If the list of tasks that each of these two original macros perform existed in one macro (without running two separate macros together), then the job of modifying to fit different vendor codes would be much easier. I'd simply swap out "VendorCode1" for whatever I needed and save it as a new module with a new name.

I'll try to sum it up differently. Here are the two tasks accomplished by my original macros. Can I have these tasks written out in one macro (not have a macro recall two other macros)?

1. Check if column E matches the specified vendor codes.
2. If so, highlight them. If not, do nothing.
3. Filter column E to show only cells that have no fill.
4. Select entire active area and delete all rows.
5. Remove filter and display all remaining lines.

Those functions exist in the two separate macros I provided, but I don't know how to combine their codes in one single macro.
 

bvbull200

New Member
Joined
Jul 29, 2013
Messages
23
Answered my own question. The combination that I posted in post # 3 actually works as is. I was making it harder than is necessary by thinking that it needed some point where it tells the macro "once you are done doing these commands, start doing these commands". This did the trick:

Code:
[COLOR=#333333]Sub HighlightVendorXYZ()[/COLOR]
Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)For Each cell In rngIf (cell.Value) = "VendorCode1" Or (cell.Value) = "VendorCode2" _ThenIf del Is Nothing ThenSet del = cellElse: Set del = Union(del, cell)End IfEnd IfNext cellOn Error Resume NextWith del.EntireRow.Interior        .Pattern = xlSolid        .PatternColorIndex = xlAutomatic        .ThemeColor = xlThemeColorDark1        .TintAndShade = -4.99893185216834E-02        .PatternTintAndShade = 0    End WithActiveSheet.Range("$A$1:$CN$285999").AutoFilter Field:=5, Operator:= _        xlFilterNoFill    Rows("2:2").Select    Range(Selection, Selection.End(xlDown)).Select    Selection.Delete Shift:=xlUp    ActiveSheet.Range("$A$1:$CN$7766").AutoFilter Field:=5 [COLOR=#333333]End Sub[/COLOR]
Thanks for the help. I will look in to your advanced solution with drop down menus and such in the near future.
 

DeusXv

Well-known Member
Joined
Jul 15, 2013
Messages
618
Answered my own question. The combination that I posted in post # 3 actually works as is. I was making it harder than is necessary by thinking that it needed some point where it tells the macro "once you are done doing these commands, start doing these commands". This did the trick:

Code:
[COLOR=#333333]Sub HighlightVendorXYZ()[/COLOR]
Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)For Each cell In rngIf (cell.Value) = "VendorCode1" Or (cell.Value) = "VendorCode2" _ThenIf del Is Nothing ThenSet del = cellElse: Set del = Union(del, cell)End IfEnd IfNext cellOn Error Resume NextWith del.EntireRow.Interior        .Pattern = xlSolid        .PatternColorIndex = xlAutomatic        .ThemeColor = xlThemeColorDark1        .TintAndShade = -4.99893185216834E-02        .PatternTintAndShade = 0    End WithActiveSheet.Range("$A$1:$CN$285999").AutoFilter Field:=5, Operator:= _        xlFilterNoFill    Rows("2:2").Select    Range(Selection, Selection.End(xlDown)).Select    Selection.Delete Shift:=xlUp    ActiveSheet.Range("$A$1:$CN$7766").AutoFilter Field:=5 [COLOR=#333333]End Sub[/COLOR]
Thanks for the help. I will look in to your advanced solution with drop down menus and such in the near future.
Yeah I was a bit confused as to what exactly the you need the main macro to do, I thought you just wanted a way to call them in one macro, not combine them. But at least you got it fixed now.

And yeah that menu option is really nice if you only have a handful of different macros but as you have loads of vendor codes it would be a little impractical.

Best of luck anyway

- DeusXv
 

Watch MrExcel Video

Forum statistics

Threads
1,090,249
Messages
5,413,305
Members
403,474
Latest member
Rampestamper

This Week's Hot Topics

Top