VBA to copy values from one sheet to next column on another sheet

[Equine Investor]

Hi,

I have a workbook Sheet1, which has stockmarket prices in column O.
Column A has the stock code.

This data is copied to Sheet2
Column A Sheet2 has the stock code
Column B has the stock price copied from Sheet1 column O.

What I'm trying to get, is if the price changes in Sheet1 column O, (for any stock), it is copied to Sheet2 in the next available column for that stock.

I currently have a macro that does the initial part:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A5:A45").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Sheets("Sheet1").Select
    Range("O5:O45").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("C1").Select
End Sub

 

[ColinKJ]

Hi Equine Investor,

You could try something like this in your sheet1 Private Sub Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 15 Then
Val2 = Cells(Target.Row, Target.Column)
Co = Sheets("Sheet2").Range("IV" + Mid$(Str$(Target.Row), 2)).End(xlToLeft).Column + 1
Sheets(2).Cells(Target.Row, Co) = Val2
End If
End Sub

ColinKJ

 

[Equine Investor]

Thanks Colin,

I tried pasting it in the VBA module, but it doesn't seem to do anything even though the prices have changed?

 

[ColinKJ]

Hi Equine Investor,

What changes the value in the Sheet 1 Column O, do you do it manually, or is it a formula.

ColinKJ

 

[Equine Investor]

Colin,

The price is displayed in column O from software which logs the prices to excel in column O.
Basically it's a web query.
The data is always logged to Sheet1 column O.

 

[ColinKJ]

Hi Equine Investor,

Sorry about this, a couple of more questions.

In colum O on sheet 1, is there a value or a formula.?

Do you have the web query code in your WB?

Who / What activates the web query?

ColinKJ

 

[Equine Investor]

No problems Colin, I really appreciate your efforts to help me.

In Sheet 1 column O there is only a value.

There is no formula, it is just populated by a price and each time the program refreshes the prices changes if it has physically changed elsewhere (on the web).
External software does the web query, it is not part of the workbook code.

The workbook just displays the price, but if the price changes in Sheet1 column O, (for any stock), I'd like it to be copied to Sheet2 in the next available column for that stock.

So you'd have something like this on sheet2

Stock  Price                             

AAA    2.70     2.80   2.64     2.48
AAB   15.00   14.80  16.00   16.20
ABD     3.00    3.90  
CAH   29.00 
EML     5.00    5.10    5.20

 

[ColinKJ]

Hi Equine Investor,

The code I did creates the record table you describe whenever the value in Sheet1 Column O is changed manually. However, our problem appears to be the software that carries out the web query update, switches off the Application Event function, which in turn prevents the code I did from running. Consequently my code doesn’t detect the changes.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

If ok with you, I'll post another thread to see if anyone on the forum can assist.

ColinKJ

 

[ColinKJ]

Hi Equine Investor,

Try the following.

Remove the code from the Sheet 1 Private Sub Worksheet_Change Event.

Install this in the Sheet1 Calculate Event

Private Sub Worksheet_Calculate()
CheckCopy
End Sub

Then install the following routine in a Standard Module:

Sub CheckCopy()
R = Sheets(1).Range("A65536").End(xlUp).Row
For a = 1 To R
    If Sheets(1).Cells(a, 15) <> Sheets(2).Cells(a, Sheets("Sheet2").Range("IV" + Mid$(Str$(a), 2)).End(xlToLeft).Column) Then
        Sheets(2).Cells(a, Sheets("Sheet2").Range("IV" + Mid$(Str$(a), 2)).End(xlToLeft).Column + 1) = Sheets(1).Cells(a, 15)
    End If
Next a
End Sub

ColinKJ