PDA

View Full Version : Run Excel Macros IF...



bapcki1
11-12-2011, 07:10 AM
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.

Using a great thread posted by Zack Barresse, 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!!


Paddy

Rob342
11-12-2011, 07:40 AM
Paddy

The macro does not work automatically put the code in either:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

it will only work if the cell =1

Rob

bapcki1
11-12-2011, 07:53 AM
Hi Rob!

Thank you for your reply!

Im afraid my VBA knowledge is minimal, I can record Macros but I haven't used the VBA editor before now so I'm not so sure what I'm meant to do..

I've copied and pasted both of the codes into the VBA code right at the end of what I had before, just before 'End Sub' I must be doing it wrong cos it didn't work.

Can you help again please?

Thank you for your reply and your patience!

Paddy

mancubus
11-12-2011, 09:54 AM
hi and wellcome VBAX.

x-posted here:
http://www.excelforum.com/excel-programming/801055-running-excel-macros-automatically-if.html

pls refer here for x-posting:
http://www.excelguru.ca/forums/faq.php?faq=crossposting#faq_crossposting_details


pls use VBA tags when posting some code.

highlight the code then click green VBA button.

Rob342
11-12-2011, 12:01 PM
Paddy

Hope you have read mancubus notes re cross posting !

Have attached a small file to have a look at ok
Right click sheet tab to look at code or press Alt+F11
Rob

bapcki1
11-14-2011, 03:02 PM
Hi Rob!

Thank you so much for the file and all your help!

Sorry about x-posting, I am new to forums and i didn't know that wasn't allowed.. sorry

I am v. grateful for your help!

Paddy