Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

  1. #1
    New Member
    Join Date
    Jul 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    Hi - Id be very grateful for any assistance with this issue - FYI, I have zero knowledge of VBA so I'm hoping for a bells and whistles piece of code that I can just cut and paste into the VBA code place!!

    I have 2 sheets.

    First Sheet is called "A".
    Second Sheet is called "ResA"

    I want to cut values from "A" and paste to "ResA".

    Specifically, when cell AB4 in Sheet A = "END", then I want to cut and paste the values in sheet A - A5:AG30 to the cell (indirect) referenced on sheet A - AD3 (eg ResA!A8) in sheet ResA.
    Hope that's reasonably clear. It appears to be quite complex to me, so Id really appreciate a dig out.

    Thanks very much

    N32

  2. #2
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    Something like this should work for you
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim wks As Worksheet, wks1 As Worksheet
        Set wks1 = Worksheets("ResA")
        If Not Intersect(Target, Range("AB4")) Is Nothing Then
            If Target.Value = "END" Then
                Range("A5:AG30").Cut wks1.Range(Range("AD3").Value)
            End If
        End If
    End Sub
    Right Click on Sheet A tab and select view code, then paste the code above in there

    Whenever cell AB4 ="END", the CUT operation is carried out
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  3. #3
    New Member
    Join Date
    Jul 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    Momentman - thanks so much for your response. The code works perfectly - almost! I should have pointed out that the macro trigger ie "END" in AB4 is actually formula driven. I know this is a worksheet change solution but I have no idea of the code required or where to insert it. Would you mind amending the code you supplied to trigger when formula results in "END" in AB4. Thanks very much once again. N32

  4. #4
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    Right click on the sheet where AB4="END" will trigger the code

    In your example above, you called the sheet A, if the sheet is Sheet A,Right click it and select View code and simply paste this revised code in there

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim wks As Worksheet, wks1 As Worksheet
        Set wks1 = Worksheets("ResA")
        If Not Intersect(Target, Range("AB4")) Is Nothing Then
            If [Target].Value = "END" Then
                Range("A5:AG30").Cut wks1.Range(Range("AD3").Value)
            End If
        End If
    End Sub
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  5. #5
    New Member
    Join Date
    Jul 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    The revised formula produces exactly same result - ie formula doesn't trigger macro but typing in "END" does??

  6. #6
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    Isn't that funny? 'cos I did test it and it works fine,#Thinking

    But it seems to work within the Worksheet_Calculate event, but the problem is, if the code runs once, it cuts and paste meaning the Source is now blank, when the worksheet calculates again, it cuts and paste but since the source is now blank, it pastes blanks over the destination and the end of the day, we achieve zilch

    I tried to use Evaluate method within the Worksheet_Change event but for some reason, its not working.

    Based on a tip by mikerickson, this should work

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim wks As Worksheet, wks1 As Worksheet
        Set wks1 = Worksheets("ResA")
        On Error Resume Next
        If Not Intersect(Target.Dependents, Range("AB4")) Is Nothing Then
            If [Target].Value = "END" Then
                Range("A5:AG30").Copy wks1.Range(Range("AD3").Value)
            End If
        End If
    End Sub
    Last edited by Momentman; Jul 24th, 2014 at 12:02 PM.
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  7. #7
    New Member
    Join Date
    Jul 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    I understand what you are saying - I am actually copying as opposed to cutting (I just edited your code). Does that change anything?

  8. #8
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    It should work now, irrespective of cut or copy. Please try and let me know how it goes
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  9. #9
    New Member
    Join Date
    Jul 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    Thanks for persevering with this. Again - maybe the original post should have said more....."END" will appear periodically in the sheet at certain intervals and each time it does I want the trigger. Bearing this in mind, should there be an else if statement to stop the trigger when AB4<>"END". The code above appeared to work, but only once! Cheers N32

  10. #10
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste values to an indirect cell (ref will change) after cell AB4 says "END"

    Since we didnt put the ELSE, it simply ignores it, which is good enough for our case

    But for me, everytime the cell changes to "END" by virtue of the formula, the required cells are copied?

    Confirm that its working or otherwise

    Thanks

    Victor
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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