Results 1 to 6 of 6

Thread: Need Help with the VBA Coding ... not even sure if it can be done by simple formula a

  1. #1

    Need Help with the VBA Coding ... not even sure if it can be done by simple formula a



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have three sheets named Sales, Production and Logistics. I have a column named Order Status. I have created three name ranges (stat1, stat2 & stat3).
    Now my question is if a user from any of the above mentioned departments choose the current status of an order, the same should be reflected in the other two sheets.


    Ex: If a guy from logistics select "pending delivery" both the sales team and Production team should see the same and vice a versa.


    Can somebody please help me with this??


    I sincerely appreciate all your help and Thanks a ton in advance.

    ** Find the sample sheet attached.
    Attached Files Attached Files

  2. #2
    Quote Originally Posted by muin View Post
    I have three sheets named Sales, Production and Logistics. I have a column named Order Status. I have created three name ranges (stat1, stat2 & stat3).
    Now my question is if a user from any of the above mentioned departments choose the current status of an order, the same should be reflected in the other two sheets.


    Ex: If a guy from logistics select "pending delivery" both the sales team and Production team should see the same and vice a versa.


    Can somebody please help me with this??


    I sincerely appreciate all your help and Thanks a ton in advance.

    ** Find the sample sheet attached.
    Can someone help me with this please... Its a bit urgent.

    Thank You for the support.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi muin,

    The following code should be placed in the ThisWorkbook module (see my signature if you need clarification on this.)

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    Dim sVal As String
    
    
        Select Case Sh.Name
            Case Is = "Sales", "Production", "logistics"
                If Target.Address = "$B$3" Then
                    Application.EnableEvents = False
                    
                    sVal = Sh.Range("B3").Value
                    Worksheets("Sales").Range("B3").Value = sVal
                    Worksheets("Production").Range("B3").Value = sVal
                    Worksheets("logistics").Range("B3").Value = sVal
                    
                    Application.EnableEvents = True
                End If
            Case Else
                'Do nothing
        End Select
    
    
    End Sub
    For reference, this will link B3 on each sheet. If you move your cells to a different location, the code will need to be updated.

    HTH,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Hi Ken,

    First of all thanks very much for the prompt reply, i tried using your code and also used ur signature link to see how it needs to be done. I tried but it didnt work !! Perhaps i am doing something wrong?. Also i need this for the whole sheet not just one cell. If you can give me your email, i shall attach my work, so that you can go through it and come up with a solution. Its important and urgent as well.

    Waiting for your reply.

    Quote Originally Posted by Ken Puls View Post
    Hi muin,

    The following code should be placed in the ThisWorkbook module (see my signature if you need clarification on this.)

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    Dim sVal As String
    
    
        Select Case Sh.Name
            Case Is = "Sales", "Production", "logistics"
                If Target.Address = "$B$3" Then
                    Application.EnableEvents = False
                    
                    sVal = Sh.Range("B3").Value
                    Worksheets("Sales").Range("B3").Value = sVal
                    Worksheets("Production").Range("B3").Value = sVal
                    Worksheets("logistics").Range("B3").Value = sVal
                    
                    Application.EnableEvents = True
                End If
            Case Else
                'Do nothing
        End Select
    
    
    End Sub
    For reference, this will link B3 on each sheet. If you move your cells to a different location, the code will need to be updated.

    HTH,

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    First off, my apologies, as that link isn't as clear for this kind of code. This code needs to go in the ThisWorkbook module, not a standard code module. So look for the ThisWorkbook object, put the code there, then remove the standard module you created.

    Now... for the whole sheet... if I give you code to do that, we're essentially creating 3 exact copies of the same worksheet. In addition to being complete duplication, it will be HORRENDOUSLY slow to do, so why?

    (There is an option to upload a workbook if you click "Go Advanced" for your reply.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Not Sure if Im in the right thread. Im looking for a code to run on a column.

    e.g
    Cells in Column D = Low, Medium, or High
    Cells in Column E = Low, Medium, or High

    Column F should have the following conditions:


    1. Green: LL(Low Probability, Low Impact), LM (Low Probability, Medium Impact), ML (Medium Probability, Low Impact)

    2. Yellow: LH (Low Probability, High Impact), MM (Medium Probability, Medium Impact), HL (High Probability, Low Impact)

    3. Red: MH (Medium Probability, High Impact), HM (High Probability, Medium Impact), HH (High Probability, High Impact).


    Please help.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •