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

N

nicky32

New Member
Joined
Jul 23, 2014
Messages
11
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. <IE - A

Hope that's reasonably clear. It appears to be quite complex to me, so Id really appreciate a dig out.

Thanks very much

N32
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
The revised formula produces exactly same result - ie formula doesn't trigger macro but typing in "END" does??
 
Upvote 0
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:
Upvote 0
I understand what you are saying - I am actually copying as opposed to cutting (I just edited your code). Does that change anything?
 
Upvote 0
It should work now, irrespective of cut or copy. Please try and let me know how it goes
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Cut a row from "sheet 1" and paste onto "sheet2", run-time error '1004' "Paste method of Worksheet class failed"
2
Replies
10
Views
694
rpaulson
rpaulson
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
165
ItalianPlatinum
I
C
  • Solved
Finding a value, then copying and pasting cells around it to another sheet
2 3 4
Replies
32
Views
1K
cboyce44
C
G
  • Solved
Indirect reference with changing cell value?
2
Replies
11
Views
1K
awoohaw
awoohaw
S
  • Question
VBA - Match and Cut & Paste
Replies
0
Views
326
syhros
S

Forum statistics

Threads
1,214,641
Messages
6,120,685
Members
448,978
Latest member
rrauni

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