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.
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
- 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.
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.
- Got everything working thank you