Automatically run vba if cell condition is met

A

AawwYeahh

New Member
Joined
Aug 10, 2017
Messages
37
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
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.
 
Upvote 0
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).
 
Upvote 0
fadee2 said:
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.
Click to expand...
Thanks, but no I have formulas as well
mumps said:
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).
Click to expand...
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
drive.google.com

Template1111(for upload).xltm

drive.google.com drive.google.com
 
Upvote 0
AawwYeahh said:
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
drive.google.com

Template1111(for upload).xltm

drive.google.com drive.google.com
Click to expand...
 
Upvote 0
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.
 
Upvote 0
Solution
fadee2 said:
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.
Click to expand...
THANK YOU that works well!
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to loop a macro
Replies
2
Views
374
rlcurlee
R
P
  • Solved
Counter in For Each loop
Replies
2
Views
197
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
173
pennbowl
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
309
Azamamir
A
C
  • Question
Loop/Repeat Macro
2
Replies
11
Views
371
kvsrinivasamurthy
K

Forum statistics

Threads
1,214,998
Messages
6,122,643
Members
449,093
Latest member
Ahmad123098

Share this page

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
Back
Top