Automatically run vba if cell condition is met

AawwYeahh

New Member
Joined
Aug 10, 2017
Messages
7
Ok so I am currently working on a more user friendly template for invoicing. I have a data validation list to choose from multiple titles and have modified worksheet to act accordingly when "topic" is picked (IE invoice, Service, etc) my problem is I need different cells (all located in same space) with different formulas. I tried writing in formula to replace if condition is met but I lose cell formatting etc. I have recorded 3 macros but am wondering how to execute them without "pulling the trigger" of clicking on control button. And these are 3 separate macros, seem inelegant and messy. I am hoping someone has a better solution.
This is the default page layout (Macro 1)
Sub Macro1()
'
' Macro1 Macro
'

' Sheets('Layout") .Select
Range("A2:O11").Select
Selection.Copy
Sheets("INVOICE").Select
Range("A16:O25").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Macro 2 (for Service)
Sheets('Layout").Select
Range("B14:O23").Select
Selection.Copy
Sheets("INVOICE").Select
Range("A16:O25").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Macro 3 (for construction)
Sheets('Layout").Select
Range("A26:O35").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INVOICE").Select
Range("A16:O25").Select
ActiveSheet.Paste
End Sub
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
hi,
If you are only pasting values to your layout and not the formulas, then use
VBA Code:
 Selection.PasteSpecial Paste:=xlPasteValues
instead of
VBA Code:
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=False

also I'd suggest use IF flag for each of your categories, e.g.
lets assume your selection criteria is in cell Z1 (invoice, service, construction)

VBA Code:
If Sheets("invoice").Range("z1").Value = "Invoice" Then
    Sheets("layout").Activate
    Sheets("layout").Range("A2:O11").Copy
    Sheets("INVOICE").Activate
    Sheets("invoice").Range("A16:O25").Select
    Selection.PasteSpecial Paste:=xlPasteValues
Else
    If Sheets("invoice").Range("z1").Value = "Service" Then
        Sheets("layout").Activate
        Sheets("layout").Range("B14:O23").Copy
        Sheets("INVOICE").Activate
        Sheets("invoice").Range("A16:O25").Select
        Selection.PasteSpecial Paste:=xlPasteValues
    Else
        If Sheets("invoice").Range("z1").Value = "Construction" Then
            Sheets("layout").Activate
            Sheets("layout").Range("A26:O35").Copy
            Sheets("INVOICE").Activate
            Sheets("Invoice").Range("A16:O25").Select
            Selection.PasteSpecial Paste:=xlPasteValues
        End If
    End If
End If

Hope that answers the question.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Rather than trying to decipher your code and without seeing how your data is organized, it would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

AawwYeahh

New Member
Joined
Aug 10, 2017
Messages
7
hi,
If you are only pasting values to your layout and not the formulas, then use
VBA Code:
 Selection.PasteSpecial Paste:=xlPasteValues
instead of
VBA Code:
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=False

also I'd suggest use IF flag for each of your categories, e.g.
lets assume your selection criteria is in cell Z1 (invoice, service, construction)

VBA Code:
If Sheets("invoice").Range("z1").Value = "Invoice" Then
    Sheets("layout").Activate
    Sheets("layout").Range("A2:O11").Copy
    Sheets("INVOICE").Activate
    Sheets("invoice").Range("A16:O25").Select
    Selection.PasteSpecial Paste:=xlPasteValues
Else
    If Sheets("invoice").Range("z1").Value = "Service" Then
        Sheets("layout").Activate
        Sheets("layout").Range("B14:O23").Copy
        Sheets("INVOICE").Activate
        Sheets("invoice").Range("A16:O25").Select
        Selection.PasteSpecial Paste:=xlPasteValues
    Else
        If Sheets("invoice").Range("z1").Value = "Construction" Then
            Sheets("layout").Activate
            Sheets("layout").Range("A26:O35").Copy
            Sheets("INVOICE").Activate
            Sheets("Invoice").Range("A16:O25").Select
            Selection.PasteSpecial Paste:=xlPasteValues
        End If
    End If
End If

Hope that answers the question.
Thanks, but no I have formulas as well
Rather than trying to decipher your code and without seeing how your data is organized, it would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Thank you for the suggestion. I tried modifying above but receive Compile Error Invalid Outside Procedure. I have validation list in M1 So I thought it may because vba didn't want to reference it, so I made K2 to point at but no luck. I do have button (but would ideally like to do when topic is selected in M1 validation
 

AawwYeahh

New Member
Joined
Aug 10, 2017
Messages
7

ADVERTISEMENT

Thanks, but no I have formulas as well

Thank you for the suggestion. I tried modifying above but receive Compile Error Invalid Outside Procedure. I have validation list in M1 So I thought it may because vba didn't want to reference it, so I made K2 to point at but no luck. I do have button (but would ideally like to do when topic is selected in M1 validation
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Private Sub abc()
If Sheets("INVOICE").Range("M1").Value = "INVOICE" Then
   Sheets("LAYOUTS").Select
   Range("A2:O11").Select
   Selection.Copy
   Sheets("INVOICE").Select
   Range("A16:O25").Select
   Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Else
        If Sheets("INVOICE").Range("M1").Value = "SERVICE" Then
            Sheets("LAYOUTS").Select
            Range("a14:O23").Select
            Selection.Copy
            Sheets("INVOICE").Select
            Range("A16:O25").Select
            Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
            Else
                If Sheets("INVOICE").Range("K2").Value = "CONSTRUCTION" Then
                    Sheets("LAYOUTS").Select
                    Range("A14:O23").Select
                    Selection.Copy
                    Sheets("INVOICE").Select
                    Range("A16:O25").Select
                    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                        False, Transpose:=False
            End If
    End If
End If
End Sub

paste above code in a Module in your vba project and bind it to the button you created on Invoice sheet, (right click button, Assign Macro) and save your worksheet as Macro-Enabled Workbook. The code is working fine and goes well with the explanation of both your macro and question.

Hope that solves the problem.
 
Solution

AawwYeahh

New Member
Joined
Aug 10, 2017
Messages
7

ADVERTISEMENT

VBA Code:
Private Sub abc()
If Sheets("INVOICE").Range("M1").Value = "INVOICE" Then
   Sheets("LAYOUTS").Select
   Range("A2:O11").Select
   Selection.Copy
   Sheets("INVOICE").Select
   Range("A16:O25").Select
   Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Else
        If Sheets("INVOICE").Range("M1").Value = "SERVICE" Then
            Sheets("LAYOUTS").Select
            Range("a14:O23").Select
            Selection.Copy
            Sheets("INVOICE").Select
            Range("A16:O25").Select
            Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
            Else
                If Sheets("INVOICE").Range("K2").Value = "CONSTRUCTION" Then
                    Sheets("LAYOUTS").Select
                    Range("A14:O23").Select
                    Selection.Copy
                    Sheets("INVOICE").Select
                    Range("A16:O25").Select
                    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                        False, Transpose:=False
            End If
    End If
End If
End Sub

paste above code in a Module in your vba project and bind it to the button you created on Invoice sheet, (right click button, Assign Macro) and save your worksheet as Macro-Enabled Workbook. The code is working fine and goes well with the explanation of both your macro and question.

Hope that solves the problem.
THANK YOU that works well!
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Sub Protect()
Sheets("sheet1").Protect Password:="asdf"
End Sub

Sub Unprotect()
Sheets("sheet1").Unprotect Password:="asdf"
End Sub

Use as per your needs.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,086
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top