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

[nicky32]

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

 

[nicky32]

Hey Victor. Unfortunately, the revised code does exactly the same as original code - ie it triggers only when I type the word "END". If a formula results in "END", nothing happens. Don't give up on me.....!

N32

 

[Momentman]

Ok, i have decided to use the Worksheet_Calculate Event, which i think is more appropriate or maybe

Private Sub Worksheet_Calculate()
    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

No one is giving up on you

 

[nicky32]

Victor - thanks for revised code. When the macro triggers, it gets caught in a loop and copies values over and over and over again, even if I change AB4 not to equal "END"....

 

[Momentman]

What formula gives rise to END in cell AB4? Is it an IF, VLOOKUP or what exactly?

 

[nicky32]

It's an IF statement

 

[Momentman]

It's an IF statement

Ok, lets give this a shot

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet, wks1 As Worksheet
    Set wks = Worksheets("A")
    Set wks1 = Worksheets("ResA")    '
    
    If Not Target.HasFormula Then
        Set Rng = Target.Dependents
        If Not Intersect(Range("AB4"), Rng) Is Nothing Then
            If wks.Range("AB4").Value = "END" Then
                wks.Range("A5:AG30").Copy wks1.Range(Range("AD3").Value)
            End If
        End If
    End If
End Sub

 

[nicky32]

Victor - You've cracked it!

Thanks so much for sticking with this! Just one more tiny thing.............can you amend the code as I want to copy paste special values.....

Brialliant. Thanks. Noel

 

[Momentman]

Like this maybe

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet, wks1 As Worksheet
    Set wks = Worksheets("A")
    Set wks1 = Worksheets("ResA")    '
    
    If Not Target.HasFormula Then
        Set Rng = Target.Dependents
        If Not Intersect(Range("AB4"), Rng) Is Nothing Then
            If wks.Range("AB4").Value = "END" Then
                wks.Range("A5:AG30").Copy
                wks1.Range(Range("AD3").Value).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
            End If
        End If
    End If
End Sub

 

[nicky32]

Victor - This code creates a run - time error 1004 No cells were found. When I debug the line Set Rng = Target.Dependents is yellow which presumably means this is where the problem is.

My spreadhseet is linked to a third party piece of software which has separate pages for different products/markets. Each time I move between markets I get this error.

I realise this maight be outside your area of expertise but any idea why this might be happening?

 

[Momentman]

Victor - This code creates a run - time error 1004 No cells were found. When I debug the line Set Rng = Target.Dependents is yellow which presumably means this is where the problem is.

Did you get this same runtime error with the code before the pastespecial was added?