Forum Discussion
Formula
Based on your description, assuming you have two sheets called "Tracking" and "Invoice" and if you want to copy the cell A13 on Invoice Sheet if changed to the first empty cell after the last row with data in column A on Tracking Sheet, place the following code on the Invoice Sheet Module.
To do so, right click on Invoice Sheet Tab --> View Code and paste the code given below into the opened code window and save your workbook as Macro-Enabled Workbook.
If name of your tracking sheet is not "Tracking", change the name of tracking sheet it in the code.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim wsDest As Worksheet Set wsDest = Worksheets("Tracking") 'Assuming Tracking is the name of the Tracking Sheet, change it as required If Target.Address(0, 0) = "A13" Then If Target <> "" Then Target.Copy wsDest.Range("A" & Rows.Count).End(3)(2) End If End If End SubLet me know if this is what you were trying to achieve.
24 Replies
Hi
To run the code whenever A13 changes in the Invoice worksheet, then
You need to attach the code to cell A13 Change event:
To do that,
Right click the worksheet Tab (Invoice) >> select view code
In the VB Editor, you have 2 drop lists, from the left one select "Worksheet" from the Right one select "Change"
Then write the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A13").Address Then
Call Evaluate
End If
End SubIf you want to check if a cell is empty then proceed to the cell below without overwriting, that requires modifying the Evaluate code and let it loop before copying.
- I am not a vb person how do I make it loop? I gather I write the code on the worksheet side and then the change side I with the second list of code you typed. Is this correct? Thank you for all this help.
Hi again
In my answer I focused on "SELECTING" the cell below assuming that you want this cell to be selected.
However if "Selecting A4" is not a must then, without a doubt go with the nice formula provided by our team mate Subodh_Tiwari_sktneer
Thank you
Nabil Mourad
- I need the code to run when the data changes in the invoice sheet a13 so to copy the info to a tracking sheet.
- Also reasoning for checking cell a3 was so not to over write the data. I need the code to check the last cell in the column with data then move down to next vacant cell and Copy data from invoice sheet a13 into the black cell. Sorry for the confusion.
I do not think this can be done with a formula, because a conditional formula returns a value but not a selection.
However it could be done by a code in VBA. Also you need to decide how would you like to run that code: Is it manually? or with the opening event of the workbook or with the change happening to a cell you specify?
I need more details or better a sample file to do it properly.
The concept is as follows
ALT + F11 >> opens the VB editor
ALT + I + M >> creates a module
then type:
Sub Evaluate()
If Range("A3"). value <> "" Then
Range("A4"). select
ActiveCell.value= Sheets("Invoice").Range("A13").value
End If
End Sub
Hope that helps
Nabil Mourad
