Excel-run macro when cell is populated 2

[Sabrina224]

I know I've seen this somewhere before, but darn if I can find it. I'm creating a tracking spreadsheet where once the user enters a copy ID in column A, the remaining columns (B-E) are populated with formulas to to lookup the previous history based on the copy ID entered. I've already created the macro and lookup tables, but I want the macro to run automatically on the specified row once the copy ID has been entered. (I would just copy all the formulas down the spreadsheet, but that just makes the file too large and it runs too slowly. It seems to me that this should be rather easy, but other than using push buttons to run macros, I'll admit, I'm a little clueless how to get started on this one! Any and all help greatly appreciated!

 

[Crystalyzer]

What if you placed your macro in the sheet that you want the cells copied in like this:

 Private Sub Worksheet_Change(ByVal Target As Range)
  'If statement to check if active cell is the cell you want to use as the trigger
  '[Your Macro here]
End Sub

Lloyd

 

[Sabrina224]

This macro should run as soon as the cell in column A is no longer empty. During the course of the next six months, I would expect 1,400 rows to be entered. If I understand you correctly, it looks like I would need an if stmt for each possible row? -- Is it possible to run a macro from within an IF statement in the spreadsheet?

 

[Crystalyzer]

All you would need is an if statement to check if the active cell is in column "a" as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range
  Dim s As Integer
  Set r = ActiveCell
  s = r.Row
  If r.Column = 1 Then
    Range("b1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Cells(s, 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
  End If
End Sub

One caveate is that you must not have "Move selection after enter" checked under "tools\options" or you may wind up with the formulas copied in row you didn't anticipate.

Hope this helps
Lloyd

 

[xlbo]

Actually, all you need to do is use the keyword TARGET

In the case of worksheet EVENTS, TARGET refers to the event cell, so for worksheet CHANGE, TARGET refers to the cell that is changed. For the worksheet SELECTION CHANGE event, it refers to the cell that is selected

This removes any need to check / uncheck "Move after enter"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Crystalyzer]

Awesome....thanks Geoff!

 

[Crystalyzer]

With Geoff's expert advice I would change my original code to this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim s As Integer
  s = Target.Row
    If Target.Column = 1 Then
    Range("b1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Cells(s, 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Cells(s + 1, 1).Select
  End If
End Sub
[\code]

This will work if you change any cell in column "A" and the cells in column "A" do not need to be contiguous.

Lloyd

 

[aug4570]

I'm a newbie w/VB and have the same problem but I don't understand what I need to do with the Worksheet_Change sub to have it automatically run my macro when I enter information in the cell. Where do I put this code on the worksheet and do I get it to run the macro? Thanks.

 

[xlbo]

right click on sheet tab
choose "View Code"
Choose "Worjsheet" from the left side dropdown
Choose "Change" from the right side dropdown

Open help
Type "Event"
Read help files

Type "target"
Read help files

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question