Forum Discussion

Copper Contributor

Sep 20, 2019

Solved

Formula

I am needing a formula that will check cell a3 if has data go down to next cell, then copy the data from another worksheet called invoice and the cell is a13.

excel

Subodh_Tiwari_sktneer
Sep 21, 2019
Jtandme
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 Sub

Let me know if this is what you were trying to achieve.

Jtandme

Copper Contributor

Sep 21, 2019

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.

Subodh_Tiwari_sktneer

Silver Contributor

Sep 21, 2019

Jtandme

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 Sub

Let me know if this is what you were trying to achieve.

Jtandme
Copper Contributor
Sep 21, 2019
Got everything working thank you
- Subodh_Tiwari_sktneer
  Silver Contributor
  Sep 21, 2019
  Jtandme
  You're welcome! Glad it worked as desired.
  Please take a minute to accept the post with proposed solution which resolved the issue as an Answer to mark your question as Solved.
  - Jtandme
    Copper Contributor
    Sep 22, 2019
    I am new here how do I accept as solved?
Jtandme
Copper Contributor
Sep 21, 2019
That worked perfect, my next thing is when it copies invoice sheet a13 to tracking sheet column a is there a way it can copy the data from invoice sheet cells d4,d5,d36 to the next blank cells in tracking sheet columns c, d, e? I think that can be put into same macro just like a13 is. Not sure.
- Jtandme
  Copper Contributor
  Sep 23, 2019
  So I thought everything was working fine and the first issue is but when I put a formula in cell c2,d2 and e2 it copies the data form the invoice sheet. But when I input a new acct # it updates c2 row and the c3 row. I do not want it to update the previous rows because that messes up the tracking info. How can I do this?
  - Subodh_Tiwari_sktneer
    Silver Contributor
    Sep 23, 2019
    Jtandme
    Can you please share the Change Event Code you finally have on Sheet Module?
    And then explain what it does and what do you expect it to do actually?