+ Reply to Thread
Results 1 to 9 of 9

Worksheet_change event on multiple target cells (if copy paste multiple cells)

  1. #1
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    10
    Posts
    179

    Worksheet_change event on multiple target cells (if copy paste multiple cells)

    Hello,

    I need help with the following codes. I have 8 cells named "ChangeCell" and changing each one will trigger a different copy paste. It works now when changing one cell either manually or through copy & paste but I need this to be more flexible to allow the user to copy paste multiple cells at once (any number of cells within the ChangeCell range will trigger it and all of them need to be updated)

    PHP Code: 
    Private Sub Worksheet_Change(ByVal Target As Range)


    If 
    Not Application.Intersect(TargetRange("ChangeCell")) Is Nothing Then
          
             
    If Target.Address "$AI$8" Then

             Range
    ("AE9").Value Target.Offset(, 1).Value
         
             End 
    If
             If 
    Target.Address "$AI$9" Then
          
             Range
    ("AE10").Value Target.Offset(, 1).Value
            
                  End 
    If
             If 
    Target.Address "$AI$10" Then
          
             Range
    ("AE15").Value Target.Offset(, 1).Value
            
                 End 
    If
                 
                 If 
    Target.Address "$AI$16" Then
          
             Range
    ("AE16").Value Target.Offset(, 1).Value
            
                 End 
    If
             If 
    Target.Address "$AI$11" Then
        
             Range
    ("AE8").Value Target.Offset(, 1).Value
             
                      End 
    If
             If 
    Target.Address "$AI$13" Then
          
             Range
    ("AE17").Value Target.Offset(, 1).Value
           
                         End 
    If
             If 
    Target.Address "$AI$14" Then
           
             Range
    ("AE19").Value Target.Offset(, 1).Value
             
             End 
    If
          If 
    Target.Address "$AI$15" Then
           
             Range
    ("AE18").Value Target.Offset(, 1).Value
             
    End 
    If
    End If
     
    Application.EnableEvents True
    End Sub 
    thanks!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Worksheet_change event on multiple target cells (if copy paste multiple cells)

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    10
    Posts
    179

    Re: Worksheet_change event on multiple target cells (if copy paste multiple cells)

    ChangeCell - Sheet1 AI8 - AI16
    This is part of a financial model where a user goes in to update actuals. I use Dec as the catch-all month to adjust for the difference between quarterly forecasts (ChangeCell) and the actuals. If the month is marked as A (Sheet2, row 10), grab actuals from Sheet1, if the month is marked as F, I set the returned value to be 0, just to be simple. in reality, if it is F, then grab values from forecast part of the model. the model needs to be adjusted when a quarterly forecast becomes available so current year always reflects the most updated quarterly forecast. I did it this way to avoid circular reference. The codes reside in sheet1. Please someone help me change the codes so that when a user copy paste into Changecell regardless of how many cells being pasted, the codes will do the same thing to every cell updated.

    thanks so much!
    Attached Files Attached Files

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Worksheet_change event on multiple target cells (if copy paste multiple cells)

    Maybe

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    10
    Posts
    179

    Re: Worksheet_change event on multiple target cells (if copy paste multiple cells)

    Thanks so much for replying, I'm going to test it out. I tried to post a new thread under Formulas but I got a message saying posting is blocked. Can someone please let me know why?

  6. #6
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    10
    Posts
    179

    Re: Worksheet_change event on multiple target cells (if copy paste multiple cells)

    attached is the popup...
    Attached Images Attached Images

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Worksheet_change event on multiple target cells (if copy paste multiple cells)

    Does the post you're trying to make include lines with stuff like <>A1, i.e. the chevron marks. Sucuri thinks these are associated with hypertext code tags and complains. Put spaces between the chevrons. e.g. < > A1

  8. #8
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    10
    Posts
    179

    Re: Worksheet_change event on multiple target cells (if copy paste multiple cells)

    Thanks so much!!

  9. #9
    Forum Contributor
    Join Date
    06-19-2014
    Location
    Toronto, Canada
    MS-Off Ver
    10
    Posts
    179

    Re: Worksheet_change event on multiple target cells (if copy paste multiple cells)

    Hello just report back the codes work like a charm! thanks so much for your kind help Richard!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 11-04-2019, 11:29 AM
  2. [SOLVED] Copy Multiple Cells from Multiple Sheets and paste them in Master Sheet
    By Ravana in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-13-2018, 07:30 AM
  3. Multiple changes in Worksheet_Change(ByVal Target As Range)
    By MixP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2018, 06:49 PM
  4. Replies: 14
    Last Post: 04-18-2017, 07:07 PM
  5. [SOLVED] multiple Sub Worksheet_Change(ByVal Target As Range)
    By hoss88 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2014, 04:39 AM
  6. [SOLVED] Worksheet_Change(ByVal Target As Range) - set to only update when 3 target cells changed?
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 06:40 PM
  7. Replies: 0
    Last Post: 07-10-2006, 04:34 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1