Excel cell data not updating until i press enter

%3CLINGO-SUB%20id%3D%22lingo-sub-2394683%22%20slang%3D%22en-US%22%3EExcel%20cell%20data%20not%20updating%20until%20i%20press%20enter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2394683%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-quilt-column%20lia-quilt-column-18%20lia-quilt-column-left%20lia-quilt-column-main-left%22%3E%3CDIV%20class%3D%22lia-quilt-column-alley%20lia-quilt-column-alley-left%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CDIV%20class%3D%22MessageSubject%22%3E%3CDIV%20class%3D%22MessageSubjectIcons%20%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3Ei%20am%20using%20stock%20market%20software%20which%20supports%20export%20live%20data%20to%20excel.%26nbsp%3B%20Now%20i%20want%20capture%20all%20the%20volume%20and%20price%20tick%20shown%20in%20excel%20to%20be%20copied%20in%20new%20sheet%20for%20further%20analysis%20how%20can%20i%20do%20this.%26nbsp%3B%20pls%20help%20i%20am%20used%20this%20code%20but%20i%20need%20to%20press%20enter%20everytime.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3EIf%20Not%20Intersect(Target%2C%20Range(%22A%3AA%22))%20Is%20Nothing%20Then%3C%2FP%3E%3CP%3EActiveCell.Offset(-1%2C%200).Activate%3C%2FP%3E%3CP%3Ea%20%3D%20Sheets(%22Sheet2%22).Cells(Rows.Count%2C%20(%22A%22)).End(xlUp).Row%20%2B%201%3C%2FP%3E%3CP%3ESheets(%22Sheet2%22).Range(%22A%22%20%26amp%3B%20a).Value%20%3D%20ActiveCell.Value%3C%2FP%3E%3CP%3EActiveCell.Offset(1%2C%200).Select%3C%2FP%3E%3CP%3EElse%3C%2FP%3E%3CP%3EIf%20Not%20Intersect(Target%2C%20Range(%22B%3AB%22))%20Is%20Nothing%20Then%3C%2FP%3E%3CP%3EActiveCell.Offset(-1%2C%200).Activate%3C%2FP%3E%3CP%3Eb%20%3D%20Sheets(%22Sheet2%22).Cells(Rows.Count%2C%20(%22B%22)).End(xlUp).Row%20%2B%201%3C%2FP%3E%3CP%3ESheets(%22Sheet2%22).Range(%22B%22%20%26amp%3B%20b).Value%20%3D%20ActiveCell.Value%3C%2FP%3E%3CP%3EActiveCell.Offset(1%2C%200).Select%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22AddMessageTags%20lia-message-tags%20lia-component-message-view-widget-tags%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fcommunity-discussion%2Fexcel-cell-data-not-updating-until-i-press-enter%2Fm-p%2F2391622%23%22%20target%3D%22_blank%22%3EAdd%20tags%3C%2FA%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2394683%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor
 

i am using stock market software which supports export live data to excel.  Now i want capture all the volume and price tick shown in excel to be copied in new sheet for further analysis how can i do this.  pls help i am used this code but i need to press enter everytime.

 

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:A")) Is Nothing Then

ActiveCell.Offset(-1, 0).Activate

a = Sheets("Sheet2").Cells(Rows.Count, ("A")).End(xlUp).Row + 1

Sheets("Sheet2").Range("A" & a).Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

Else

If Not Intersect(Target, Range("B:B")) Is Nothing Then

ActiveCell.Offset(-1, 0).Activate

b = Sheets("Sheet2").Cells(Rows.Count, ("B")).End(xlUp).Row + 1

Sheets("Sheet2").Range("B" & b).Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

End If

End If

End Sub

0 Replies