Forum Discussion

Jtandme's avatar
Jtandme
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's avatar
    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.

nabilmourad's avatar
nabilmourad
MVP
Sep 21, 2019

Jtandme 

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

 

 

 

Resources