HELP!! Run Excel Macros IF..?

[bapcki1]

Hi!

I desperately need some help with some excel macros, can anybody help please?

I need the VBA code so that an excel macros will run once automatically when, for example cell A4 = 1.

I've managed to get this far with the VBA:

Sub copytohere()
If ThisWorkbook.Sheets("Sheet1").Range("A4").Value = "1" Then
Range("A1").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
End If
End Sub

I dont think I've got it quite right though...
Can anyone help please?

This macros is basic - all it does is copy data from cell A1 to E1 and then deletes A1.
I was hoping the IF statement would make the macros run once automatically when cell A4 = 1 however when I enter 1 into cell A4 nothing happens.

What is interesting is that if i then go to run the macros manually, it will perform the macros correctly if cell A4 = 1 .If cell A4 doesn't equal 1 and I run the macros manually, nothing happens.

I need the VBA so that the macros will run once automatically when cell A4 equals 1.

Any help is much appreciated -Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!


Paddy

 

[dave3009]

Ideally you'd change A4 to something other than 1 at the end of the macro so it doesn't run again

Sub copytohere()
If ThisWorkbook.Sheets("Sheet1").Range("A4").Value = "1" Then
    Range("A1").Copy
    Range("E1").PasteSpecial xlPasteValues
    Range("A1").ClearContents
End If
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = "0"
End Sub

 

[bapcki1]

Hi Dave!

Thank you for your reply!

Ahhh! I see, so now, when I manually run the macros it changes the 1 to an 0 to end the process..

Do you know how to make the macros run automatically when i enter 1 into cell A4 and hit 'enter' ?


Thank you so much for your help!


Paddy

 

[dave3009]

If you put this in the code module for the sheet in question

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$4" Then Exit Sub
If Target.Value = 1 Then
    copytohere
End If
End Sub

 

[bapcki1]

DAVE YOU ARE A GENIUS!!!

THANK YOU SO MUCH FOR YOUR HELP!! :D :D


Paddy


(it worked btw :P)